SQL Server General Pivoting-CodePudding

I have this SQL table called Comments with 2 columns: NCC_CLTID and NCC_CTYPE

The table has the following information

NCC_CLTID   NCC_CTYPE
TEST1         A 
TEST1         A 
TEST1         C
TEST1         E
TEST1         E
TEST1         E 
TEST1         E
TEST2         A
TEST2         B
TEST2         B
TEST2         C

I want a pivot table that looks like the following:

NCC_CLTID TYPE1 TYPE2 TYPE3 TYPE4 TYPE5 TYPE6 TYPE7 TYPE8 ... TYPE20
TEST1       A    A      C     E    E      E     E    NULL      NULL
TEST2       A    B      B     C    NULL   NULL  NULL  NULL      NULL

How can I achieve this? I can't see a way to make this work for some reason

CodePudding user response：

Assuming you have known or maximum number of TYPEs, you can use row_number() to determine the column

Example

Select *
 From (
        Select [NCC_CLTID]
              ,[NCC_CTYPE]
              ,Item = concat('Type',row_number() over (partition by [NCC_CLTID] order by [NCC_CTYPE]) )
        from YourTable
      ) src
 Pivot (max([NCC_CTYPE]) for Item in ([Type1],[Type2],[Type3],[Type4],[Type5],
                                      [Type6],[Type7],[Type8],[Type9],[Type10],
                                      [Type11],[Type12],[Type13],[Type14],[Type15],
                                      [Type16],[Type17],[Type18],[Type19],[Type20]
                                     ) ) pvt

Results