SQL資料表里的行轉列(動態)，并將后面對應列的數量匯總。
需要轉列的資料是動態的，所以用靜態轉列的方法不能實作，
請幫忙指點。
uj5u.com熱心網友回復：
--資料源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
uj5u.com熱心網友回復：
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')
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/233711.html
標籤：C#
上一篇：vb.net如何用代碼實作panel和其中控制元件復制
下一篇：安裝VS2017失敗 未能安裝包“Microsoft.Ancm.IISExpress.Msi,version=12.2.18292,chip=x64”。