嘿我有这张table:
WING Presents Waiting CT
-----------------------------------------------------------------------
A 30 10 1
B 40 20 4
C 26 8 2
D 17 14 3
我想将其转置为:
A B C D
--------------------------------------------------------------------
Present 30 40 26 17
Waiting 10 20 8 14
CT 1 4 2 3
我已经尝试了这个代码,从一个问题,我看到这里的StackOverflow:
drop table if exists #b
select R.*, row_number() over (order by R.WING) as seqnum
into #b
from #Res R (my original table is #Res)
select name,
max(case when seqnum = 1 then val end),
max(case when seqnum = 2 then val end),
max(case when seqnum = 3 then val end),
max(case when seqnum = 4 then val end)
from #b cross apply
(values (1, 'WING', 'WING'),
(2, 'Presents', 'Presents'),
(3, 'Waiting', 'Waiting'),
(4, 'CT', 'CT'))
v(ord, name, val)
group by name, ord
order by ord;
而且不起作用...有没有人能帮帮我??非常感谢!
1条答案
按热度按时间hwazgwia1#
在SQL Server中,您可以使用UNPIVOT和PIVOT函数。在这种情况下,您必须知道要转换的所有值:
UNPIVOT获取列列表并将其转换为行,然后PIVOT执行最终的列转换。
dbfiddle