Combine multiple rows into multiple columns-CodePudding

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;

CodePudding user response：

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