在Oracle中，对来自多个列的数据按唯一ID进行分组

Question

我搜索了无数关于我的场景的帖子，但似乎找不到实现我想要的结果的答案。我可能没有问对问题，这可能就是我找不到答案的原因。

我正在寻找一种方法，通过GRPID组结果成单行，并跨多列显示结果。

查询

SELECT GRPID, TYPE, NAME, TID, DATE
FROM TABLE1 AS S
INNER JOIN TABLE2 AS D ON D.TID = S.TID
WHERE 
S.GRPTYPE = 'MEDIA'
AND S.GRP_ID IN ('1234','5678')
AND D.STATUS = 'ACTIVE'
AND D.TYPE IN ('TALENT', 'MANAGEMENT')

当前结果

GRPID   Type        Name            TID     Date
1234    Talent      Jim Halpert     6259    1-Nov-05
1234    Management  Michael Scott   1411    1-Dec-00
5678    Talent      Dwight Schrute  8732    4-Mar-04

预期结果

GRPID   Type_1  Name_1          TID_1   Date_1      Type_2      Name_2          TID_2   Date_2
1234    Talent  Jim Halpert     6259    1-Nov-05    Management  Michael Scott   1411    1-Dec-00
5678    Talent  Dwight Schrute  8732    4-Mar-04    null        null            null    null

我真诚地感谢您提供的任何指导。

Answer 1

如果你只有固定类型('TALENT'， 'MANAGEMENT')，你可以在这里使用动态枢轴。

SELECT GRPID,
'Talent' AS Type_1,
MAX(CASE WHEN D.TYPE IN ('TALENT') THEN Name END) AS Name_1,
MAX(CASE WHEN D.TYPE IN ('TALENT') THEN TID END) AS TID_1,
MAX(CASE WHEN D.TYPE IN ('TALENT') THEN Date END) AS Date_1,
'MANAGEMENT' AS Type_2,
MAX(CASE WHEN D.TYPE IN ('MANAGEMENT') THEN Name END) AS Name_2,
MAX(CASE WHEN D.TYPE IN ('MANAGEMENT') THEN TID END) AS TID_2,
MAX(CASE WHEN D.TYPE IN ('MANAGEMENT') THEN Date END) AS Date_2
FROM TABLE1 AS S
INNER JOIN TABLE2 AS D ON D.TID = S.TID
WHERE S.GRPTYPE = 'MEDIA'
AND S.GRP_ID IN ('1234','5678')
AND D.STATUS = 'ACTIVE'
AND D.TYPE IN ('TALENT', 'MANAGEMENT')
GROUP BY GRPID;