脚本: declare @n int = 2 create table #t1 ( [月份] nvarchar(50), [业务员] nvarchar(50), [金额] int ) insert
脚本:
declare @n int = 2
create table #t1
(
[月份] nvarchar(50),
[业务员] nvarchar(50),
[金额] int
)
insert into #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 [月份] order by [金额] desc)
, *
into #t2
from #t1
--select * from #t2
create table #t3
(
[列序号] int,
[行序号] int,
[月份] nvarchar(50),
[结果] nvarchar(50),
[类型] nvarchar(50)
)
insert into #t3
select [列序号] = 1, [行序号], [月份], [结果] = [业务员], [类型] = [月份] + N"-业务员"
from #t2
insert into #t3
select [列序号] = 2, [行序号], [月份], [结果] = [金额], [类型] = [月份] + N"-金额"
from #t2
--select * from #t3
declare @sqlIn nvarchar(max) = ""
select @sqlIn = @sqlIn + case when @sqlIn <> "" then "," else "" end + "[" + [类型] + "]"
from (select distinct [列序号], [月份], [类型] from #t3) a
order by [月份], [列序号]
--select @sqlIn
--行列转换后的目标数据
declare @sql nvarchar(max) = "
select top " + cast(@n as nvarchar(10)) + @sqlIn + "
from (select [行序号], [结果], [类型] from #t3) D
pivot(max([结果]) for [类型] in (" + @sqlIn + ")) Q
order by [行序号]
"
exec(@sql)
drop table #t1, #t2, #t3
脚本运行结果:
【转载请注明博文来源:https://www.cnblogs.com/zhang502219048/p/13173228.html】
--结束END--
本文标题: sql server如何通过pivot对数据进行行列转换(进阶)
本文链接: https://www.lsjlt.com/news/6799.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0