Sql-server – Unpivot with rows converted to columns as well as columns converted to rows

sql-server-2005t-sqlunpivot

I've tried to understand unpivot examples on the web but they don't quite result in what I'm trying to do, which is to have specific rows turned into columns and columns turned into rows. To try and illustrate what I mean I have included a picture…

enter image description here

I believe that the data in a given column needs to be of the same type, so if that's the case then it's ok to have all the columns as varchar. The data in the source table will not need to be part of any sums, just displayed as-is. I am just trying to 'rotate' this to display the data in a vertical way for end-user consumption.

Best Answer

This query uses both UNPIVOT and then PIVOT:

SELECT piv.[Data], piv.x, piv.y
FROM (
    SELECT [Type], ColA = CAST(ColA as varchar(10)), ColB = CAST(ColB as varchar(10)), ColC = CAST(ColC as varchar(10)), ColD = CAST(ColD as varchar(10))
    FROM @data
) d
UNPIVOT (
    [value] FOR [Data] IN (ColA, ColB, ColC, ColD)
) as unpiv
PIVOT (
    MAX([value]) 
    FOR [Type] IN ([x], [y])
) as piv
;
  • It first unpivots your data in order to get a regular table
  • It then pivots this table back to the new required format

I have to cast everything as varchar. Based on your data model, you may have to cast them to another type. An aggregate function is required by PIVOT. Since this is a 1 to 1 match, it works with either of MAX or MIN.

With SQL Server >= 2008 you can also replace UNPIVOT by Table Value Constructor and CROSS APPLY along with PIVOT:

SELECT piv.[Data], piv.x, piv.y
FROM (
    SELECT v.[Type], [value], [Data]
    FROM @data d
    CROSS APPLY (values
        (d.[type], CAST(d.ColA as varchar(10)), 'ColA')
        , (d.[type], CAST(d.ColB as varchar(10)) , 'ColB')
        , (d.[type], CAST(d.ColC as varchar(10)), 'ColC')
        , (d.[type], CAST(d.ColD as varchar(10)), 'ColD')
    ) as v([type], [value], [Data])
) unpiv
PIVOT (
    MAX([value])
    FOR [Type] IN ([x], [y])
) as piv
;

Without PIVOT/UNPIVOT you can use this query with any (old) version of SQL Server:

SELECT [Data]
    , [x] = MAX(CASE WHEN [type] = 'x' THEN [value] END)
    , [y] = MAX(CASE WHEN [type] = 'y' THEN [value] END)
FROM (
    SELECT [type], [value] = CAST(ColA as varchar(10)), [Data] = 'ColA' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColB as varchar(10)), [Data] = 'ColB' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColC as varchar(10)), [Data] = 'ColC' FROM @data
    UNION ALL
    SELECT [type], [value] = CAST(ColD as varchar(10)), [Data] = 'ColD' FROM @data
) as v
GROUP BY [Data]

Output:

Data    x       y
ColA    123456  654321
ColB    $500    $200
ColC    6       36
ColD    30      90

Data:

Declare @data table([Type] char(1), ColA bigint, ColB varchar(10), ColC int, ColD int);
INSERT INTO @data([Type], ColA, ColB, ColC, ColD) VALUES
    ('x', 123456, '$500', 6, 30)
    , ('Y', 654321, '$200', 36, 90);
Related Question