SQL group data based on a column and pivot rows to (unknown) columns

July 25, 2022

I’m trying to pivot a synonyms table into an unknown number of columns (T-SQL). I’m using SQL Server 2017.

I have this table:

  fd_Id |  fd_Word  |   fd_Interpretation
---------------------------------------------
    1   |   smile   |          1
    2   |   grin    |          1
    3   |   laugh   |          1
    4   |   see     |          2
    5   |   detect  |          2
    6   |   look    |          2
    7   |   peek    |          2
    8   |   walk    |          3
    9   |   stroll  |          3

fd_Id is an identity column and synonyms are grouped by fd_Interpretation. While synonyms can have any number of rows (10+), in practice they are around 6 to 8.

This is the desired output:

   Id   |   Word1   |   Word2   |   Word3   |   Word4   |   Wordn...
---------------------------------------------------------------------
   1    |   grin    |   laugh   |   smile   |           |
   2    |   detect  |   look    |   peek    |   see     |
   3    |   stroll  |   walk    |           |           |

Id is a ROW_NUMBER or RANK. Optional if too much trouble.

I looked at other similar PIVOT row questions here but couldn’t find one that is close enough.

Any help is highly appreciated.

>Solution :

A straight-up PIVOT in concert row_number() with should do the trick

Select *
 From (
        Select ID = fd_Interpretation
              ,Item = concat('Word',row_number() over (partition by fd_Interpretation order by fd_Word) )
              ,Value = fd_Word
         From  YourTable
      ) src
 Pivot ( max( Value ) for Item in ([Word1],[Word2],[Word3],[Word4],[Word5],[Word6],[Word7],[Word8]) ) pvt