Rows to Column in ORACLE

Question

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.

Possible duplicate question with answers: http://stackoverflow.com/questions/7730111/pivoting-rows-into-columns-dynamically-in-oracle?rq=1 — jpw, Sep 28 '15 at 17:33

score 4 · Answer 1 · edited Sep 28 '15 at 19:25

4

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,

edited Sep 28 '15 at 19:25

riz

69
1
7

answered Sep 28 '15 at 17:18

Vamsi Prabhala

48,685
4
36
58

Thanks vkp. Actually the GROUPA is not fixed. – riz Sep 28 '15 at 17:23
Perfect. I thought we need a procedure but this is perfect. – Utsav Sep 28 '15 at 17:35
I ran your query and get ORA-00936 MISSING EXPRESSION error at SELECT DISTINCT. Yes, I would need three columns. – riz Sep 28 '15 at 17:49
got to know that sub queries can only be used with xml. see if it works. one more option would be to hardcode all possible groupa values instead of a subquery. – Vamsi Prabhala Sep 28 '15 at 17:59
I am almost there. With your solution I get this result. See my answer. – riz Sep 28 '15 at 18:22
@riz..Gordon's solution would get you what you need. selecting only 3 groupa per name – Vamsi Prabhala Sep 28 '15 at 19:46

Gordon Linoff · Accepted Answer · 2015-09-28T23:15:26.783

2

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;

edited Sep 28 '15 at 23:15

answered Sep 28 '15 at 19:40

Gordon Linoff

1,242,037
58
646
786

Gordon..could you shed some light on how to get the output as a table instead of xml when using pivot? – Vamsi Prabhala Sep 28 '15 at 19:41
Thanks Gordon. With minor revision it worked. I modified line 5 as: from (select t.*, .... line 7: from table1 t) .... and line 8 as ) – riz Sep 28 '15 at 20:09
@vkp . . . That is a question that should be a question not a comment. – Gordon Linoff Sep 28 '15 at 23:16

Rows to Column in ORACLE

2 Answers2

Linked