Could somebody please help me how to achieve the following?
Table:
ID NAME ROLE
1 KONDA LEAD
1 SATHI CO-LEAD
1 JOHN CO-LEAD
2 REDDY LEAD
2 SURESH CO-LEAD
3 PRASAD LEAD
My output should look like
ID LEAD CO-LEAD_1 CO-LEAD_2
1 KONDA SATHI JOHN
2 REDDY SURESH
3 PRASAD
Thanks Inadvance.
CodePudding user response:
We can use conditional aggregation with the help of ROW_NUMBER()
:
WITH cte AS (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY ID, ROLE ORDER BY NAME) rn
FROM yourTable t
)
SELECT
ID,
MAX(CASE WHEN ROLE = 'LEAD' THEN NAME END) AS LEAD,
MAX(CASE WHEN ROLE = 'CO-LEAD' AND rn = 1 THEN NAME END) AS "CO-LEAD_1",
MAX(CASE WHEN ROLE = 'CO-LEAD' AND rn = 2 THEN NAME END) AS "CO-LEAD_2",
FROM cte
GROUP BY ID
ORDER BY ID;
To create a view, use:
CREATE VIEW yourView AS
WITH cte AS (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY ID, ROLE ORDER BY NAME) rn
FROM yourTable t
)
SELECT -- same as above