Query to transform table in SQL

Question

My query is as below

SELECT SupplierId,StakeholderSupplierId,Percentage
FROM std_Stakeholders    
ORDER BY SupplierId

which yields me result as below

SupplierId        StakeholderSupplierId             Percentage
1                          3175                        68.00
2929                       5504                        25.00
5504                        1                          68.25
5504                       3238                        50.00
5504                       2810                        23.00

I want to transform this output as .

  SupplierId    StakeholderSupplierId1  Percentage  StakeholderSupplierId2  Percentage StakeholderSupplierId3   Percentage
    1                    3175            68.00  
    2929                 5504            25.00
    5504                  1              68.68             3238                50.00                2810            23.00

I tried with joins and pivoting . couldn't succeed in writing a proper query to get the desired output . Can anyone help me out

Note: StakeholderSupplierId is not static . it may vary from 1 to n

Answer 1

I don't know if this is a solution for your problem (depends on what you need to do after this query), but for problems like this I have use the MySQL GROUP_CONCAT function. For a similar solution in SQL Server check this question.

With a GROUP_CONCAT function you can get a result table like this:

SupplierId    StakeholderSupplierIds  Percentages  
1             3175                    68.00  
2929          5504                    25.00
5504          1, 3238, 2810           68.68, 50.00, 23.00