bjurstrs bjurstrs - 2 months ago 5
SQL Question

Transpose table in SQL

I have a table set up in the following manner.

CustomerNumber June15_Order June15_Billing July15_Order July15_Billing August15_Order August15_Billing
12345 5 55 3 45
5431 6 66 5 67


I would prefer it to be:

CustomerNumber Date Order Billing
12345 01/06/2015 5 55
12345 01/07/2015 3 45
5431 01/06/2015 6 66
5431 01/07/2015 5 67


Any thoughts as to how I would accurately transpose this table?

Answer

If you're just trying to get the old data into the new, you'll basically need to use brute force:

 INSERT INTO NewTable
     (CustomerNumber, [Date], [Order], Billing)
     ( 
        SELECT CustomerNumber, '06/15/2015', June15_Order, June15_Billing
        FROM OldTable

        UNION

        SELECT CustomerNumber, '07/15/2015', July15_Order, July15_Billing
        FROM OldTable

        UNION

        SELECT CustomerNumber, '08/15/2015', August15_Order, August15_Billing
        FROM OldTable
    )
Comments