sqlserver如何通过pivot对数据进行行列转换（进阶）

Z时代
2024-01-10
分类：综合

database

脚本：

/*
说明：sql server如何通过pivot对数据进行行列转换（进阶），用于员工业绩按月龙虎排行榜、客户销售金额按月排行榜等
脚本来源：https://www.cnblogs.com/zhang502219048/p/13173228.html
作者：zhang502219048
作者微信公众号：SQL数据库编程（微信号zhang502219048）
*/
declare@nint=2
createtable #t1
(
[月份]nvarchar(50),
[业务员]nvarchar(50),
[金额]int
)
insertinto #t1
values(N"2020-01", N"业务员1", N"100")
    , (N"2020-01", N"业务员2", N"200")
    , (N"2020-02", N"业务员2", N"300")
    , (N"2020-02", N"业务员3", N"400")
    , (N"2020-03", N"业务员4", N"500")
    , (N"2020-03", N"业务员5", N"600")
    , (N"2020-04", N"业务员1", N"700")
    , (N"2020-04", N"业务员3", N"800")
--select * from #t1
select[行序号]= row_number() over(partition by[月份]orderby[金额]desc)
     , *
into #t2
from #t1
--select * from #t2
createtable #t3
(
[列序号]int,
[行序号]int,
[月份]nvarchar(50),
[结果]nvarchar(50),
[类型]nvarchar(50)
)
insertinto #t3
select[列序号]=1, [行序号], [月份], [结果]=[业务员], [类型]=[月份]+ N"-业务员"
from #t2
insertinto #t3
select[列序号]=2, [行序号], [月份], [结果]=[金额], [类型]=[月份]+ N"-金额"
from #t2
--select * from #t3
declare@sqlInnvarchar(max) =""
select@sqlIn=@sqlIn+casewhen@sqlIn<>""then","else""end+"["+[类型]+"]"
from (selectdistinct[列序号], [月份], [类型]from #t3) a
orderby[月份], [列序号]
--select @sqlIn
--行列转换后的目标数据
declare@sqlnvarchar(max) ="
select top "+cast(@nasnvarchar(10)) +@sqlIn+"
from (select [行序号], [结果], [类型] from #t3) D
pivot(max([结果]) for [类型] in ("+@sqlIn+")) Q
order by [行序号]
"
exec(@sql)
droptable #t1, #t2, #t3