ORACLE中的行到列 [英] Rows to Column in ORACLE

问题描述

这是查询的结果.分组有很多，但最多三个与一个人相关联.

This is a result of a query. There are many groupas but maximum three are associated to a person.

NAME     ID    GROUPA
=====================
James    20    A
James    20    B
James    20    D
Michael  30    A
Michael  30    B
Michael  30    C
Jordan   40    I

我想要的是...

NAME     ID    GROUP1     GROUP2     GROUP3
===========================================
James    20    A          B          D
Michael  30    A          B          C
Jordan   40    I

我尝试了此查询，但是它不起作用...

I tried this query but its not working...

SELECT NAME, ID, GROUPA
FROM 
    (SELECT NAME, ID, 
        ROW_NUMBER() OVER (PARTITION BY GROUPA ORDER BY EMPLOYEE_ID) AS GROUP
    FROM TABLE1)
GROUP BY NAME, ID, GROUPA

感谢您的指导.

推荐答案

您可以通过条件聚合和row_number()来做到这一点:

You can do this with conditional aggregation and row_number():

select name, id,
       max(case when seqnum = 1 then groupa end) as group1,
       max(case when seqnum = 2 then groupa end) as group2,
       max(case when seqnum = 3 then groupa end) as group3
from (select t.*,
             row_number() over (partition by name order by employee_id) as seqnum
      from table1 t
     ) t
group by name, id;

这篇关于ORACLE中的行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！