列轉行，再行轉列

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里面的部分基本相同，为转置的栏位名