Transposing column into rows and create new column?

Question

I have the following table

Col_1 Col_2 Col_3     Date
 0.1   0.2   0.3   2013-08-13
 0.4   0.5   0.6   2013-08-12

I want the following:

   Date          New Column        Value
2013-08-13        Col_1             0.1
2013-08-13        Col_2             0.2
2013-08-13        Col_3             0.3
2013-08-12        Col_1             0.4
2013-08-12        Col_2             0.5  
2013-08-12        Col_3             0.6

So, I am not only transposing the table but also adding a new column which tells me column name from which the values are coming. I tried doing a UNION..but it did not work :(

Thanks!

Answer 1

You could it like this:

INSERT INTO NEW_TABLE_NAME (Date, `New Column`, Value)

SELECT    DATE, 'Col_1', Col_1

FROM      OLD_TABLE_NAME


UNION ALL


INSERT INTO NEW_TABLE_NAME (Date, `New Column`, Value)

SELECT    DATE, 'Col_2', Col_2

FROM      OLD_TABLE_NAME


UNION ALL


INSERT INTO NEW_TABLE_NAME (Date, `New Column`, Value)

SELECT    DATE, 'Col_3', Col_3

FROM      OLD_TABLE_NAME;