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

What I want is...

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

Thank for your guidance.

回答1:

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;

回答2:

select name,id, 
 max(case when groupa = 'A' then groupa end) as group1,
  max(case when groupa = 'B' then groupa end) as group2,
  max( case when groupa = 'C' then groupa end) as group3
   from tablename
   group by name, id

if the number of groupa is fixed the query above would work.

Edit: Using pivot

 select * from
  (select name, id , groupa from tablename)
  pivot xml (
  max(groupa) for groupa in
   (select distinct groupa from tablename)
   )

---

Thanks for the solution. I am almost there. This is what I get after running this query. Actually there are 53 different GROUPS so it is adding 53 columns but maximum number of groups assigned to a user are 5.

NAME      ID      A      B      C     D      E      F      G      H
James     20      A      null   null  null   null   null   null   H
Michael   30      A      B      null  null   E      null   null   null

How do I get results like this...

NAME      ID      GROUP_1  GROUP_2  GROUP_3
James     20      A        H
Michael   30      A        B        E

How do I get my result as I mentioned in the question? Thanks,