如何解决oracle中的子查询返回多个值

Question

谁能告诉我如何实现以下目标?

表:

ID      NAME       ROLE
1       KONDA      LEAD
1       SATHI      CO-LEAD
1       JOHN       CO-LEAD
2       REDDY      LEAD
2       SURESH     CO-LEAD
3       PRASAD     LEAD

我的输出应该像

ID      LEAD       CO-LEAD_1       CO-LEAD_2
1       KONDA      SATHI           JOHN
2       REDDY      SURESH
3       PRASAD

谢谢Inadvance。

Answer 1

我们可以在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;

创建视图，使用:

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