drop table if exists #t1
select a.BusinessId,a.MRPTime,b.PartNo,c.Type,c.WeekSeq,c.ValueA,c.ValueB,c.ValueC,c.ValueD
into #t1
from CTB_Master a with(nolock) left join CTB_MaterialInfo b with(nolock) on a.BusinessId=b.BusinessId
left join CTB_DateValue c with(nolock) on b.InfoId=c.InfoId
where PlantNo='P112' and VendorCode='C617986201' and a.MRPTime>'2025-04-21'
drop table if exists #t2
select * into #t2 from #t1
unpivot (val for ValueType in (ValueA,ValueB,ValueC,ValueD)) as u
drop table if exists #t3
DECLARE @cols AS NVARCHAR(MAX), @sql AS NVARCHAR(MAX);
select weekseq into #t3 from (SELECT DISTINCT WeekSeq FROM #t2 ) b order by weekseq
SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME(WeekSeq)
FROM #t3 AS WeekSeq;
SET @sql = 'SELECT BusinessId,MRPTime,PartNo,ValueType ' + @cols + ' FROM
(SELECT BusinessId, MRPTime, PartNo,ValueType,val,weekseq FROM #t2) x
PIVOT
(SUM(val) FOR weekseq IN (' + @cols + ')) p ';
--print @sql
EXEC sp_executesql @sql;
注意事项:
1)unpivot /PIVOT 前面的select中一定要将转换的行或列包含在里面.(列转行的时候我尝试了多次);
2)for IN中间的部分 行转列 和 列转行 是不同的. 行转列为需要转的栏位名[unpivot (val for ValueType in (ValueA,ValueB,ValueC,ValueD)]，
列转行为转出来的栏位名.[PIVOT (SUM(val) FOR weekseq IN (' + @cols + ')];
3) IN里面的部分基本相同，为转置的栏位名
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。