Unpivot
-
The table I am using has NULL values but when I unpivot it I only see the weeks which has values. The Nulls are all gone. How can I display that in Unpivot?
Here is the Query
SELECT * FROM dbo.test
Unpivot(Hours for details in ( [Week_1]
,[Week_2]
,[Week_3]
,[Week_4]
,[Week_5]
,[Week_6]
,[Week_7]
,[Week_8]
,[Week_9]
,[Week_10]
)) as Unpvt
Tried using ISNULL in the first line : SELECT ISNULL(Week1,0)Week1 FROM dbo.test but gives me an error : Invalid Column
Also gives me an error when I do an insull inside Unpivot.
Any Ideas.....
-
Quick thought, use cross-tab
😎
Can you post DDL and sample data?
-
Try using CROSS APPLY instead of UNPIVOT
SELECT name,Hours,details FROM #Temp
CROSS APPLY
(
VALUES
([Week_1],'Week_1'),
([Week_2],'Week_2'),
([Week_3],'Week_3'),
([Week_4],'Week_4'),
([Week_5],'Week_5'),
([Week_6],'Week_6'),
([Week_7],'Week_7'),
([Week_8],'Week_8'),
([Week_9],'Week_9'),
([Week_10],'Week_10')
) c(Hours,details)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
ok here it is :
Create Table #Temp
(Name Varchar(255) Null
,Week_1 Int
,Week_2 Int
,Week_3 Int
,Week_4 Int
,Week_5 Int
,Week_6 Int
,Week_7 Int
,Week_8 Int
,Week_9 Int
,Week_10 Int
)
Insert Into #Temp (Name , Week_1 ,Week_2 ,Week_3 ,Week_4 ,Week_5 ,Week_6,Week_7 ,Week_8 ,Week_9 ,Week_10 )
Values ('Oscar',Null, 5,10,15,20,25,30,35,40,Null)
Select * from #Temp
Unpivot(Hours for details in ( [Week_1]
,[Week_2]
,[Week_3]
,[Week_4]
,[Week_5]
,[Week_6]
,[Week_7]
,[Week_8]
,[Week_9]
,[Week_10]
)) as Unpvt
If you see the Unpivot is missing Week_1 and Week_10 due to NUll.
-
Works! Thank you so much
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply