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

how do that help ? . its not recursive . its just transformation of row to columns with row numbers — Kuntady Nithesh, Apr 06 '12 at 05:27
it is recursive: you are looking for unique supplierids, if you see a repeat then you put the data in the next column — Alex Gordon, Apr 06 '12 at 05:52
How can that result come from that query? It says `WHERE SupplierId=5504`, yet the result has all kinds of supplier IDs. — wallyk, Apr 06 '12 at 06:43
You'll need to write dynamic sql since you don't know the max number of stakeholders. — Asken, Apr 07 '12 at 06:07

score 1 · Answer 1 · edited May 23 '17 at 12:27

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

score 0 · Answer 2 · answered Apr 06 '12 at 07:40

Well, you can do this with a two-step process but equally I expect it is possible with a PIVOT - however as an example (old style approach for the last step):

declare @stakeholdersForPivot table
(
  SupplierId int,
  StakeholderId int,
  StakeholderSupplierId int,
  Percentage decimal(19,2)
)

; with orderedStakeholders as
(
  select SupplierId
  , ROW_NUMBER() over (partition by SupplierId order By StakeholderSupplierId) StakeholderId
  ,StakeholderSupplierId, Percentage
  from std_Stakeholders

)

insert into @stakeholdersForPivot (SupplierId, StakeholderId, StakeholderSupplierId, Percentage)
select SupplierId, StakeholderId, StakeholderSupplierId, Percentage
from orderedStakeholders

select SupplierId
, sum(case when StakeholderId = 1 then StakeholderSupplierId else null end) StakeholderSupplierId1
, sum(case when StakeholderId = 1 then Percentage else null end) Percentage1
, sum(case when StakeholderId = 2 then StakeholderSupplierId else null end) StakeholderSupplierId2
, sum(case when StakeholderId = 2 then Percentage else null end) Percentage2
, sum(case when StakeholderId = 3 then StakeholderSupplierId else null end) StakeholderSupplierId3
, sum(case when StakeholderId = 3 then Percentage else null end) Percentage3
from @stakeholdersForPivot
group by SupplierId
order by SupplierId

Obviously you have to be explicit about the maximum number of stakeholder suppliers you're expecting per supplier but you can extend this as required.

score 0 · Answer 3 · answered Apr 06 '12 at 09:57

Here's the query that uses the "GROUP BY MAX" trick. Should work across databases. The drawback is that the columns list is static.

SELECT
    supplierid,
    max(StakeholderSupplierId1) AS StakeholderSupplierId1,
    max(percentage1) AS percentage,
    max(StakeholderSupplierId2) AS StakeholderSupplierId2,
    max(percentage2) AS percentage,
    max(StakeholderSupplierId3) AS StakeholderSupplierId3,
    max(percentage3) AS percentage
FROM
    (
        SELECT
            supplierid,
            CASE WHEN ranking = 1 THEN stakeholdersupplierid END AS StakeholderSupplierId1,
            CASE WHEN ranking = 1 THEN percentage END AS percentage1,
            CASE WHEN ranking = 2 THEN stakeholdersupplierid END AS StakeholderSupplierId2,
            CASE WHEN ranking = 2 THEN percentage END AS percentage2,
            CASE WHEN ranking = 3 THEN stakeholdersupplierid END AS StakeholderSupplierId3,
            CASE WHEN ranking = 3 THEN percentage END AS percentage3
        FROM
            (
                SELECT
                    supplierid,
                    stakeholdersupplierid,
                    percentage,
                    rank() OVER (PARTITION BY supplierid ORDER BY percentage DESC) AS ranking
                FROM
                    std_stakeholders
            ) AS t
    ) AS t
GROUP BY
    supplierid
;

Query to transform table in SQL

3 Answers3