I have been trying to generate dataset that looks like as shown in the last table.
Any suggestion would be very helpful. Thank you
-
1See Using PIVOT and UNPIVOTTomas Pastircak– Tomas Pastircak2014-04-23 13:25:52 +00:00Commented Apr 23, 2014 at 13:25
Add a comment
|
2 Answers
/*
Set up temp table to work off
*/
create table #temp
(a decimal(10,2),
b decimal(10,2),
c decimal(10,2),
d decimal(10,2),
e decimal(10,2),
f decimal(10,2),
dollor decimal(10,2),
YrQtr nvarchar(7))
insert #temp values
(105,5,8,51,40,15,29039.56,'2012-Q4'),
(109,5,5,49,40,14,16116.72,'2013-Q1'),
(109,4,4,55,41,22,21988.31,'2013-Q2'),
(105,3,4,52,36,21,14971.17,'2013-Q3'),
(93,3,2,47,35,18,25862.77,'2013-Q4')
*/
select
[Measure],[2012-Q4], [2013-Q1], [2013-Q2], [2013-Q3], [2013-Q4]
from(
select A, B, C, D, E, F, Dollor [zDollar],YrQtr
from #temp) as Source
UNPIVOT (
vals
for [Measure] in([A],[B],[C],[D],[E],[F],[zDollar])
)as [unpivoted]
pivot (
max(vals)
for YrQtr in ([2012-Q4], [2013-Q1], [2013-Q2], [2013-Q3], [2013-Q4])
) as t
One thing to note is that this script will order the resulting measures alphabetically - so it would be A,B,C,D,Dollor,E,F rather than the likely desired A,B,C,D,E,F,Dollor. To make it more simple for now, I just aliased the Dollar column to force it to the end of the list.
3 Comments
poshan
Tom, thank you. Thats exactly what I wanted to accomplish. One thing though how do I get rid of .00 from measure (A-F). Thank you
Tom721
Oh ya, I forgot to mention that - the pivot columns have to be the same type, so I had to change the column from int to decimal. Not sure how you can get around this, as the column can be either one or the other, but not both
poshan
Thank you Tom, what I did was instead of changing to decimal data type, I just changed Dollar column to Int ( all other columns are Int). That way I dont have to worry about the trailing zeros. Dollar columns gives me rounded figure which is just fine. Once again thank you
Tom, thank you, I RETURN THE QUERY AS BELOW
DECLARE @TEMPQUES TABLE(MaxRange NVARCHAR(MAX),MinRange NVARCHAR(MAX),ReferenceTypeName NVARCHAR(MAX),Instructions NVARCHAR(MAX),Question NVARCHAR(MAX))
insert into @TEMPQUES
SELECT CAST(MaxRange AS nVARCHAR(100)),CAST(MinRange AS NVARCHAR(100)),QuestionnaireReferenceTypeName,Instructions,Question FROM vw_QuestionnaireAnswers WITH(NOLOCK) WHERE QuestionnaireID = 100000
SELECT [QA],[Dimenssion],[Is Material in good packing],[NCR],[Special remarks],[Length] FROM
(SELECT ISNULL(MaxRange,0) AS MaxRange,ISNULL(MinRange,0) AS MinRange,
ISNULL(ReferenceTypeName,'NA') AS ReferenceTypeName
,ISNULL(Instructions,'NA') AS Instructions,Question
FROM @TEMPQUES ) AS Questionnaire
UNPIVOT( VALUE FOR [QA] IN([MaxRange],[MinRange],[ReferenceTypeName],[Instructions])
) AS [UNPIVOTED]
PIVOT(MAX(VALUE) FOR Question IN ([Dimenssion],[Is Material in good packing],[NCR],[Special remarks],[Length] ) ) AS PVTTable
