September 10, 2010 at 9:40 am
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!
September 10, 2010 at 6:05 pm
The issue you have here is that you are trying to carry out a 4 column PIVOT with the conventional single PIVOT statement. One way to overcome this difficulty is to first UNPIVOT the data so the 4 columns become the single column called col. These 4 columns are also marked with an ID (1,2,3,4) to form blocks to be used later. Now we have shaped the data as required with the StepID, the ID to locate each unpivoted block and the data originally in columns ShortDesc, Type_1, ar1, ar2 all in the single column col. Here I used CROSS APPLY to unpivot the data but you could also use the UNPIVOT statement if you prefer. You can see the result of this initial UNPIVOT by uncommenting the SELECT * statement below. Now we have the shaped data in the cte, we can carry out the conventional PIVOT statement to transform the data as required.
;WITH cte (StepID, ID, col) AS
(
SELECT P.StepID, Z.ID, Z.col
FROM ProductionTest AS P
CROSS APPLY
(
SELECT 1, ShortDesc UNION ALL
SELECT 2, CONVERT(VARCHAR, Type_1) UNION ALL
SELECT 3, CONVERT(VARCHAR, ar1) UNION ALL
SELECT 4, CONVERT(VARCHAR, ar2)
) AS Z (ID, col)
)
--SELECT * FROM cte ORDER BY ID, stepid
SELECT [1] AS Step1, [2] AS Step2, [3] AS Step3, [4] AS Step4, [5] AS Step5
FROM cte
PIVOT (MAX(col) FOR StepID IN ([1], [2], [3], [4], [5])) AS pvt
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply