不定个数的行转列代码示例

--TODO:待完善，补充实际数据
select distinct LoanRemainingTerm
into #term
from #data
 
declare @cols nvarchar(max), @query nvarchar(max)
SET @cols = STUFF
(
  (
    SELECT ',' + QUOTENAME(LoanRemainingTerm)
    from (
      select *
      from #term
    ) x
    FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)'),1,1,''
)
 
select @query =  'select TrustID, ScenarioID, ' + @cols + ' from
                  (
                       select * from #data
                  ) x
                  pivot
                  (
                       max(Percentage) for LoanRemainingTerm in (' +  @cols + ')
                  ) p'
exec (@query)