3

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.

5
  • do you have a fixed number of row or columns? or are they both unlimited? Dec 2, 2015 at 13:55
  • Yes. Both cols and rows are fixed. There are just two rows and their values are predetermined (known in advance). I could do this with unions, and multiple queries, and temp tables but for performance (avoidance of several time consuming queries to get individual bits of data) I want to try and do it with unpivot.
    – MrVimes
    Dec 2, 2015 at 13:57
  • 1
    you have to unpivot and pivot it again Dec 2, 2015 at 14:07
  • I'll see if I can work that out. One thing I've never got the hang of with sql is pivots. Edit - I see you've provided an answer. Thankyou. I'll try to get this working with the real data and then come back to the question.
    – MrVimes
    Dec 2, 2015 at 14:15
  • with real data, you just have to replace names by real names or add missing columns. Dec 2, 2015 at 14:31

1 Answer 1

4

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);
2
  • Thankyou. Transposed my real columns and it worked perfectly. :)
    – MrVimes
    Dec 2, 2015 at 16:17
  • The CROSS APPLY method of unpivoting can be used in 2005 too, you'd only need to replace the table value constructor with a series of SELECTs combined via UNION ALL.
    – Andriy M
    Dec 3, 2015 at 7:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.