更新时间:2022-11-28 20:56:32
First is with a Static Pivot. A static pivot is when you know the data ahead of time to turn into columns.
select *
from
(
select name, id, convert(char(5), dt, 101) dt, jobid, amount
from test
) x
pivot
(
sum(amount)
for dt in ([01/02], [02/02], [03/02], [04/05], [05/05])
)p
order by jobid, name
请参见带有演示的SQL提琴
第二种方法是使用动态PIVOT 在运行时标识要转换为列的值.
The second way is by using a Dynamic PIVOT to identify at run-time the values to turn to columns.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(char(5), dt, 101))
from test
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT name, id, ' + @cols + ', jobid from
(
select name, id, convert(char(5), dt, 101) dt, jobid, amount
from test
) x
pivot
(
sum(amount)
for dt in (' + @cols + ')
) p
order by jobid, name'
execute(@query)
请参见带有演示的SQL提琴
两者都会产生相同的结果.当您不知道要提前转换为列的值时,动态"效果很好.
Both will produce the same results. The Dynamic works great when you do not know the values ahead of time to convert to columns.