Pivot data from one table to another?

Question

Following on from my question here and some useful advice.

I currently have a table where the columns are age ranges (0-10, 10-20 etc.) and contain an integer population value.

I have created a new table into which I would like to pivot the data from the first table and insert it into the new table where one row from the first table would require seven rows in the new table. E.G. for one row:

Id | Region |  0 | 10 | 20 | 30 | 40 | 50
-----------------------------------------
01 | London | 24 | 45 | 38 | 29 | 36 | 49

becomes

Id | Region | AgeRange | Population
-----------------------------------
 1 |   1    |     0    |     24
 2 |   1    |    10    |     45
 3 |   1    |    20    |     38

(and the rest of the rows but you get the idea)

How would it be suggested that I go about doing this?

Answer 1

First use UNION ALL to unpivot your columns, then use variables to assign an row_number or id

SQL DEMO

SELECT t.*, @id := @id + 1 as  rn
FROM (
    SELECT Region, 0 AgeRange, `0` as Population
    FROM YourTable
    UNION ALL
    SELECT Region, 10 as AgeRange,`10` as Population
    FROM YourTable
    UNION ALL
    SELECT Region, 20 as AgeRange, `20` asPopulation
    FROM YourTable
    UNION ALL
    SELECT Region, 30 as AgeRange, `30` asPopulation
    FROM YourTable
    UNION ALL
    SELECT Region, 40 as AgeRange, `40` asPopulation
    FROM YourTable
    UNION ALL
    SELECT Region, 50 as AgeRange, `50` asPopulation
    FROM YourTable
 ) t
CROSS  JOIN (SELECT @id := 0) var
ORDER BY Region, AgeRange   

OUTPUT