sql - 如何在 SQL 中创建动态数据透视查询

Question

我正在尝试在 SQL 中创建动态数据透视查询，但我的问题是合同 id 和 tier desc 列都是动态的，我不知道如何解决这个问题。我有这样的事情：

这是我想看到的输出

标签： sqlsql-servertsql

Answer 1

这可以通过重复列名来完成，但是，我无法想象为什么要这样做。

@Col 是我们应用别名的地方...[#] as [Tier Value]...

例子

Declare @Col varchar(max) = Stuff((Select Distinct ',' + concat(QuoteName(row_number() over (Partition By ContractID Order by TierDesc)),' as [Tier Value]') From Yourtable  Order by 1 For XML Path('')),1,1,'') 
Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(row_number() over (Partition By ContractID Order by TierDesc)) From Yourtable  Order by 1 For XML Path('')),1,1,'') 

Select  @SQL = '
Select ContractID,'+@Col+'
 From (  
        Select ContractID
              ,TierDesc
              ,ColNr = row_number() over (Partition By ContractID Order by TierDesc) 
         From  YourTable
      ) Src
 Pivot (max(TierDesc) for ColNr in ('+@SQL+') ) pvt
 '
 Exec(@SQL)

退货

ContractID  Tier Value  Tier Value  Tier Value
123         tier1       tier2       NULL
555         tier4       tier5       tier6
652         tier0       tier4       NULL

编辑 - 然后生成的 SQL 看起来像这样

Select ContractID
      ,[1] as [Tier Value]
      ,[2] as [Tier Value]
      ,[3] as [Tier Value]
 From (  
        Select ContractID
              ,TierDesc
              ,ColNr = row_number() over (Partition By ContractID Order by TierDesc) 
         From  YourTable
      ) Src
 Pivot (max(TierDesc) for ColNr in ([1],[2],[3]) ) pvt

编辑 2

Select Distinct 
       ColNr = row_number() over (partition by ContractID Order By TierDesc)
 From  Yourtable