Uing PIVOT/UNPIVOT to convert rows to columns...

Question

Hi, I have been reading up on PIVOT an UNPIVOT but have not been able to get the results formatted correctly to present the data. Here is my source table:

StepID         |      ShortDesc     |     Type_1        |      ar1      |    ar2
   1                  ShortDesc1            10                11.11         11.01
   2                  ShortDesc2            20                22.22         22.02
   3                  ShortDesc3            30                33.33         33.03
   4                  ShortDesc4            40                44.44         44.04
   5                  ShortDesc5            50                55.55         55.05           

Here is the result I am trying to achieve:

 |      Step1       |       Step2     |      Step3     |     Step4      |     Step5
      ShortDesc1         ShortDesc2       ShortDesc3      ShortDesc4        ShortDesc5
         10                 20                30              40               50
        11.11             22.22             33.33            44.44           55.55
        11.01             22.02             33.03            44.04           55.05

Here is the latest of what I have tried, didn't work of course:

DROP TABLE ProductionTest
CREATE TABLE ProductionTest (StepID int, ShortDesc nvarchar(25), Type_1 int, ar1 real, ar2 real)
INSERT INTO ProductionTest  VALUES (1, 'Short Desc 1', 10, 11.11, 11.01)
INSERT INTO ProductionTest  VALUES (2, 'Short Desc 2', 20, 22.22, 22.02)
INSERT INTO ProductionTest  VALUES (3, 'Short Desc 3', 30, 33.33, 33.03)
INSERT INTO ProductionTest  VALUES (4, 'Short Desc 4', 40, 44.44, 44.04)
INSERT INTO ProductionTest  VALUES (5, 'Short Desc 5', 50, 55.55, 55.05)

SELECT * FROM ProductionTest

SELECT [1]as Step1, [2] as Step2, [3]as Step3, [4]as Step4, [5]as Step5
FROM     ( SELECT [StepID], ShortDesc, Type_1, ar1, ar2
           FROM   ProductionTest) p 
PIVOT (MAX(ShortDesc)
                     FOR StepID IN ([1], [2], [3], [4], [5])
       ) AS pvt

Thanks for your help!

Answer 1

Pivot doesn't rotate an entire table. I think you would need something like

SELECT [1]as Step1, [2] as Step2, [3]as Step3, [4]as Step4, [5]as Step5
FROM     ( SELECT [StepID], ShortDesc
           FROM   ProductionTest) p 
PIVOT (MAX(ShortDesc)
                     FOR StepID IN ([1], [2], [3], [4], [5])
       ) AS pvt

UNION ALL

SELECT [1]as Step1, [2] as Step2, [3]as Step3, [4]as Step4, [5]as Step5
FROM     ( SELECT [StepID], CAST(Type_1 AS nvarchar(25)) AS Type_1
           FROM   ProductionTest) p 
PIVOT (MAX(Type_1)
                     FOR StepID IN ([1], [2], [3], [4], [5])
       ) AS pvt

UNION ALL

SELECT [1]as Step1, [2] as Step2, [3]as Step3, [4]as Step4, [5]as Step5
FROM     ( SELECT [StepID], CAST(ar1 AS nvarchar(25)) AS ar1
           FROM   ProductionTest) p 
PIVOT (MAX(ar1)
                     FOR StepID IN ([1], [2], [3], [4], [5])
       ) AS pvt

UNION ALL

SELECT [1]as Step1, [2] as Step2, [3]as Step3, [4]as Step4, [5]as Step5
FROM     ( SELECT [StepID], CAST(ar2 AS nvarchar(25)) AS ar2
           FROM   ProductionTest) p 
PIVOT (MAX(ar2)
                     FOR StepID IN ([1], [2], [3], [4], [5])
       ) AS pvt