Combine multiple rows into multiple columns

Question

I am working on a project in which I need to combine to an existing table so I need to convert all the rows with the same ID to multiple columns.

The table looks like this, for 1 product there could be multiple commission person (max. 6) and CommissionID is unique.

ProudctID  |  Name  |   Label  | Commission| CommissionID 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 47        |  John  |   Owner  |   50.0    |   1
 47        |  Steve |   Owner  |   40.0    |   2
 47        |  Giana |   Manager|   10.0    |   3

I need to merge this table with product table for a report where for each product id, there are multiple columns with commission data.

ProudctID  |  Name1  |   Label1  | Commission1 |  Name2  |   Label2  | Commission2 |  Name3  |   Label3  | Commission3 | 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
47         |  John   |   Owner   |    50.0     |  Steve  |   Owner   |   40.0      |  Giana  |   Manager |   10.0      |

I've tried PIVOT like this but it doesn't work, I also tried to change pivot variables but it doesn't produce any results. Just NULL in the new columns.

Select * from 
    (select PC.CommissionID, 
        PC.ProductID, 
        PC.Commission, 
        PC.Name,
        PC.Label
     from ProductCommission PC
     where PC.ProductID = 47 and PC.Deleted = 0
     ) d
     pivot (max(CommissionID) for Name in ( 
     Name1, CommisionLabel1,  Commission1,
     Name2, CommisionLabel2,  Commission2,
     Name3, CommisionLabel3,  Commission3)) 
     pvt;

Since you have mixed datatypes, a conditional aggregation may be more appropriate. — John Cappelletti, Nov 24 '22 at 18:16
I've tried this union approach but my column names are kind of not dynamic so didn't work: [link](https://stackoverflow.com/questions/21692871/combine-multiple-rows-into-multiple-columns-dynamically-in-sql-server ) — beginnerdev, Nov 24 '22 at 18:17
You've tagged *dynamic-pivot* but your 3 pivoted columns appear fixed and you need a maximum of 6 groups of pivoted rows? I would agree using *pivot* is unwieldy, just aggregate using conditional case expressions. Do you have `commissionId` 1 upto-6 for each `ProductId`? — Stu, Nov 24 '22 at 18:19
@Stu Yes, `commissionId` can be upto 6 for each `ProductId`. I am beginner at SQL so thought dynamic-pivot would help, I removed that tag. — beginnerdev, Nov 24 '22 at 18:23

score 2 · Accepted Answer · answered Nov 24 '22 at 18:25

If you have a known or maximum number of columns, here is a conditional aggregation

Conditional Aggregations offer a bit more flexibility than PIVOT and they can maintain data types

Select ProductID  
      ,Name1       = max(case when RN=1 then A.Name end)
      ,Label1      = max(case when RN=1 then A.Label end)
      ,Commission1 = max(case when RN=1 then A.Commission end)
      ,Name2       = max(case when RN=2 then A.Name end)
      ,Label2      = max(case when RN=2 then A.Label end)
      ,Commission2 = max(case when RN=2 then A.Commission end)
      ,Name3       = max(case when RN=3 then A.Name end)
      ,Label3      = max(case when RN=3 then A.Label end)
      ,Commission3 = max(case when RN=3 then A.Commission end)
      ,Name4       = max(case when RN=4 then A.Name end)
      ,Label4      = max(case when RN=4 then A.Label end)
      ,Commission4 = max(case when RN=4 then A.Commission end)
 From  (
        Select *
              ,RN = row_number() over (partition by ProductID order by (select null)) 
         from YourTable
       ) A 
 Group By ProductID

Results

Instead of row_number you could just use the existing *commissionId* column — Stu, Nov 24 '22 at 18:30
@Stu I see that in the comments... However, can John and "Betsy " have the same commission rate/ID ? — John Cappelletti, Nov 24 '22 at 18:32

Combine multiple rows into multiple columns

1 Answers1