How to get value instead of fieldname in Unpivot query
This is my query
Declare @SampleUNPivot Table(ID int ,Name varchar(50),a int,b int,c int,d int)
insert into @SampleUNPivot values(1,'name1',1,2,3,4)
insert into @SampleUNPivot values(2,'name2',10,20,30,40)
insert into @SampleUNPivot values(3,'name3',11,21,31,41)
insert into @SampleUNPivot values(4,'name4',14,24,34,44)
Select ID,Name,[SampleValue]
From (
Select ID,name,a,b,c,d from @SampleUNPivot) orig
UNPIVOT
( quantity for [SampleValue] in (a,b,c,d)) as UNPT
------------Current Result-------------
ID Name SampleValue
1 name1 a
1 name1 b
1 name1 c
1 name1 d
2 name2 a
2 name2 b
2 name2 c
2 name2 d
3 name3 a
3 name3 b
3 name3 c
3 name3 d
4 name4 a
4 name4 b
4 name4 c
4 name4 d
Please correct the query above to give Results like
ID Name SampleValue
1 name1 1
1 name1 2
1 name1 3
1 name1 开发者_如何学JAVA 4
and so on..................
get value instead of fieldname in Unpivot query
That's your quantity
field.
SELECT ID,Name,[SampleValue], quantity
FROM ( SELECT ID,name,a,b,c,d from @SampleUNPivot) orig
UNPIVOT ( quantity for [SampleValue] in (a,b,c,d)) as UNPT
精彩评论