SQL数据 动态 行转列 并汇总的查询方法

[学习C#+SQL]

SQL数据表里的行转列(动态)，并将后面对应列的数量汇总。
需要转列的数据是动态的，所以用静态转列的方法不能实现，
请帮忙指点。

[燃烧的路人]

--数据源 select N'产品A' as 产品名,'2020-11-01' as 入库日期,N'202011前半月' as 入库期间,1211 as 入库数量 into #temp union all select N'产品A' as 产品名,'2020-11-02' as 入库日期,N'202011前半月' as 入库期间,1011 as 入库数量 union all select N'产品A' as 产品名,'2020-11-20' as 入库日期,N'202011后半月' as 入库期间,1021 as 入库数量 union all select N'产品B' as 产品名,'2020-11-01' as 入库日期,N'202011前半月' as 入库期间,2011 as 入库数量 union all select N'产品B' as 产品名,'2020-11-02' as 入库日期,N'202011前半月' as 入库期间,101 as 入库数量 union all select N'产品B' as 产品名,'2020-11-20' as 入库日期,N'202011后半月' as 入库期间,1011 as 入库数量 --先进行汇总 select 产品名,入库期间,入库数量 into #temp2 from #temp --动态拼接入库期间 declare @date nvarchar(max)='' select @date+=',['+入库期间+']' from #temp group by 入库期间 order by 入库期间 desc set @date = RIGHT(@date,LEN(@date)-1) --动态语句 exec(N'select * from #temp2 pivot(sum(入库数量) for 入库期间 in('+@date+')) a') drop table #temp drop table #temp2

[大葡萄八块一斤]

declare @s nvarchar(max)
Select     @s=isnull(@s+',','')+quotename(入库期间) from Product GROUP BY 入库期间  order by 入库期间 DESC
exec('select [产品名],'+@s+' from (
select 产品名,入库期间,[总入库数量]=sum([入库数量])over(partition by [产品名],[入库期间]) from Product) a 
pivot (MAX([总入库数量]) for [入库期间] in('+@s+'))b')

[拉文麦克]

我曾经做过行转列，和列转行，一般在SQL里面转，性能比较好，c#代码也可以实现，不懂可以多百度，以前我也是这样解决的。

[Dear200892]

https://blog.csdn.net/Dear200892/article/details/110383149 半个月前刚好遇到一个行转列的问题，适用于商品名未知动态。