Search code examples
sqlsql-serverpivotcrosstabunpivot

How to transform the table columns to vertical data in Sql server tables?

I would like to transform one Sql server table into another.

Original table

    Period      Date          Portfolio   Benchmark

    Pre0Month   12/31/2014   -0.0001      -0.0025
    Pre1Month   11/31/2014    0.0122       0.0269
    Pre2Month   10/31/2014    0.0176       0.0244

After transformation

    Returns      Pre0Month   Pre1Month    Pre2Month

    Portfolio   -0.0001      0.0122       0.0176
    Benchmark   -0.0025      0.0269       0.0244
Solution

  • Considering the name of the table to be MyTable, you can pivot it the following way:

    SELECT * FROM
(
  SELECT Period, [Returns], value
  FROM MyTable
  CROSS APPLY
  (
    SELECT 'Portofolio', CAST(Portofolio as varchar(10)) 
    UNION ALL
    SELECT 'Benchmark', CAST(Benchmark as varchar(10)) 
  ) c([Returns], value)
) d
PIVOT
(
    MAX(value)
    FOR Period IN (Pre0Month, Pre1Month, Pre2Month)
) piv;