Using PIVOT/UNPIVOT to convert rows to columns

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 Ste开发者_JAVA百科p2, [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!

---

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

---

Based on your comment that you would like to do this via a dynamic version. You can use Martin's version for a static version but if you want a dynamic, meaning the number of StepID's will change then you can do the following:

DECLARE @cols AS NVARCHAR(MAX),
    @colsAlias AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(StepID) 
                    from ProductionTest
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SELECT @colsAlias = STUFF((SELECT DISTINCT ',' + QUOTENAME(p.StepID) + ' AS ' + QUOTENAME('Step' + Cast(p1.StepID as varchar(10)))  
    FROM ProductionTest p INNER JOIN ProductionTest p1 ON p.StepID = p1.StepID
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')


set @query = 'SELECT ' + @colsAlias + ' from 
             (
                 select [StepID], ShortDesc
                 FROM   ProductionTest
            ) x
            pivot 
            (
                MAX(ShortDesc)
                for stepId in (' + @cols + ')
            ) p 
            UNION ALL
            SELECT ' + @colsAlias + ' from 
             (
                 select [StepID], CAST(Type_1 AS nvarchar(25)) AS Type_1
                 FROM   ProductionTest
            ) x
            pivot 
            (
                MAX(Type_1)
                for stepId in (' + @cols + ')
            ) p 
            UNION ALL
            SELECT ' + @colsAlias + ' from 
             (
                 select [StepID], CAST(ar1 AS nvarchar(25)) AS ar1
                 FROM   ProductionTest
            ) x
            pivot 
            (
                MAX(ar1)
                for stepId in (' + @cols + ')
            ) p 
            UNION ALL
            SELECT ' + @colsAlias + ' from 
             (
                 select [StepID], CAST(ar2 AS nvarchar(25)) AS ar2
                 FROM   ProductionTest
            ) x
            pivot 
            (
                MAX(ar2)
                for stepId in (' + @cols + ')
            ) p '

execute (@query)

The query is still complicated but the columns will be created when the query runs.

Here is a SQL Fiddle with a Demo