CODEWITHSUNDEEP
sqlt-sqlsql-server-2014transpose
bjurstrs
bjurstrs

Reputation: 1089

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?

Upvotes: 0

Views: 385

Answers (3)

justcode
justcode

Reputation: 1582

First create the a new table with the desired structure, after that you will need to create a stored procedure for that task, which will iterate over all rows.

On the columns you know old_col to new_col just take the value and save in a variable, for the others you will need to create condition for each month like a "contains june" and save in two variables date and value, after that each time you found a new month with value > 0 perform a insert on the new table with all the variables.

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40319

Presuming there are columns for any month and any years, this gets ugly really fast. If the columns are set and hard-coded, use @John Pasquet's solution (+1). If you need the ability to work with any set of columns of the form MMMMDD_Type, here's an outline.

First pass:

  • Write a SELECT... UNPIVOT... query to transform the table
  • Map the resulting "label" column to a Date datatype and a "Type" (Order, Billing)

However, mapping result set column names of "July15" to "Jul 1, 2015" (or 01/07/2015) is hard, if not crazy hard. This leads to a second pass:

  • Build a "lookup" list of columns from sys.tables and sys.colmns
  • Pick out those that are to be unpivoted
  • Figure out the dates and types for each of them
  • Build the SELECT... UNPIVOT... in dynamic SQL, dumping the results to a temp table
  • Join this temp table to the lookup list by original column name, which (via the join) gets you the prepared date and type values

Seriously, this could get ridiculously complex. The smart money is on rebuild the tables with columns for date and type.

Upvotes: 1

John Pasquet
John Pasquet

Reputation: 1842

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
    )

Upvotes: 4

Related Questions