Transposing only few columns in SQL Server

Question

I have 4 columns in my table like :

key     cusi             isi             name
1      46644UAQ1         US46642EAV83      A
1      46644UAR9         XS0062104145      A
1      254206AC9                           A                               
2      05617YAJ8         US86359AXP38      B      
2      885220BP7                           B
2       null                               B
3      885220BP5         885220BP7345      c

the key and name column content is getting duplicated because of the cusi and isi column .I would like to transpose only few columns in this case cusi and isi column so that i get 1 record of id =1 and another one for id=2 .In my use case there can be at the max 3 ditinct cusi or 3 isi column.

The transpose table should like

  key name cusi1        cusi2       cusi3        isi1         isi2           isi3
    1    A   46644UAQ1    46644UAR9   254206AC9  US46642EAV83  XS0062104145   NULL 
    2    A   46644UAR9    05617YAJ8   885220BP7  US86359AXP38  NULL           NULL
   3    c   885220BP5     null        null       885220BP7345  NULL           NULL 

In some cases there might be only 1 row like in t he above example it is for key= 3

i know that sql has PIVOT and UNPIVOT queries but i am not sure how to use it for transposing selecting columns of a table Any help would be of great help. Thanks

Answer 1

If you know that each key-name group will have a fixed number of records (three, based on the sample data you gave us), then an ordinary non pivot should work. In the query below, I make use of the row number to distinguish each of the three columns you want for cusi and isi in your result set.

SELECT t.key,
       t.name,
       MAX(CASE WHEN t.rn = 1 THEN cusi END) AS cusi1,
       MAX(CASE WHEN t.rn = 2 THEN cusi END) AS cusi2,
       MAX(CASE WHEN t.rn = 3 THEN cusi END) AS cusi3,
       MAX(CASE WHEN t.rn = 1 THEN isi  END) AS isi1,
       MAX(CASE WHEN t.rn = 2 THEN isi  END) AS isi2,
       MAX(CASE WHEN t.rn = 3 THEN isi  END) AS isi3
FROM
(
    SELECT key,
           cusi,
           isi,
           name,
           ROW_NUMBER() OVER(PARTITION BY key ORDER BY cusi) AS rn
    FROM yourTable
) t
GROUP BY t.key,
         t.name

Note that SQL Server also has a PIVOT function, which is an alternative to what I gave.