How to resolve subquery return more than one value in oracle

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.

Answer

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