You'll need to use the "execute immediate" functionality. For Sybase this is EXEC (@cmd) - I'm not sure what this is for MS-SQL.

A quick hack (in Sybase's T-SQL dialect) to get you on your way:

declare @sex int, @start int, @end int
declare @column varchar(32)
declare @sql varchar(255)

-- First you need to insert the column names into a temp table:
create table #col (colname varchar(32))
insert #col(colname) values('Male_00_04')
insert #col(colname) values('Female_00_04')
-- etc...

select @start = 0, @end = 4, @sex = 1

declare col_cr cursor for select colname from #col
open col_cr
fetch col_cr into @column
while(@@sqlstatus = 0)
begin
    select @sql = "INSERT INTO Adjusted_census_data_normalised 
     (GeographicCode
     , Sex
     , Age_start
     , Age_end
     , Person_count)
   select A.GeographicCode
        , " + convert(varchar, @sex) 
        + ", " + convert(varchar, @start) 
        + ", " + convert(varchar, @end) 
        + ", A." + @column + "
    FROM        Adjusted_census_data A 
   WHERE       " + @column + " >= 0"

    exec (@sql)
    
    if (@sex = 1)
        select @sex = 2
    else
        select @sex = 1
    if (@sex = 1)
    begin
        select @start = @start +5
        if (@start > 84)
          select @end = 115
        else
          select @end = @end+5
    end

    fetch col_cr into @column
end

close col_cr
deallocate cursor col_cr
[download]

There are probably a bunch of errors in this code, but it should give you a starting point. Michael