Query to convert a table to SQL
My request is below
SELECT SupplierId,StakeholderSupplierId,Percentage
FROM std_Stakeholders
ORDER BY SupplierId
which gives me the result 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 convert 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've tried using joins and pivots. could not create a correct query to get the desired result. Can anyone help me?
Note. StakeholderSupplierId is not static. it can range from 1 to n
source to share
I don't know if this is the solution to your problem (depends on what you need to do after this query), but for problems like this I use a MySQL function GROUP_CONCAT
. For a similar solution in SQL Server, check this question.
With a function, GROUP_CONCAT
you can get the 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
source to share
Well, you can do it with a two-step process, but I equally expect it to be possible with 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 should clearly state the maximum number of stakeholder suppliers that you expect from a supplier, but you can renew it as needed.
source to share
Here's a query using the "GROUP BY MAX" trick. Should work through databases. The downside is that the column 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
;
source to share