开发者

How to get value instead of fieldname in Unpivot query

开发者 https://www.devze.com 2022-12-17 08:16 出处:网络
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)

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
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号