How to create dynamic pivot query in SQL

Question

I am trying to create dynamic pivot query in SQL but my issue is that the contract id and the tier desc columns are both dynamic and I could not figure out how to solve this issue. I have something like this:

and this is the output I would like to see

You can't have all three columns in your output named "tier value". Each column needs a different name. In what way is this pivot dynamic? — Tab Alleman, Jun 12 '19 at 19:06
okay but my goal to display the corresponding tier value for each contract id so how do i do that? thanks — moe, Jun 12 '19 at 19:13
You say display. Can they be delimited in one column? If so, use a group by and the FOR XML STUFF. Just google those words if you want the delimited list — KeithL, Jun 12 '19 at 19:14
@TabAlleman "can't" is a really big word. I think the key word here is "shouldn't" :) — John Cappelletti, Jun 12 '19 at 19:33

John Cappelletti · Accepted Answer · 2019-06-12T19:56:45.987

This can be done with repeating column names, however, I can't imagine why one would want this.

The @Col is where we apply the Alias ...[#] as [Tier Value]...

Example

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)

Returns

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

EDIT - Then generated SQL Looks like this

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

EDIT 2

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

one last thing, the first query is working fine but the order of the tier value is not right, i would like to show as tier1, tier2 etc... — moe, Jun 12 '19 at 20:17
@moe We are ordering by TierDesc. Perhaps edit your questions and provide a better sample data — John Cappelletti, Jun 12 '19 at 20:31

How to create dynamic pivot query in SQL

1 Answers1