Multiple rows to one row Oracle SQL

Question

I have the following table

P_ID, PROGR, DATA  
 1  ,  1   , 'DATO A'
 1  ,  2   , 'DATO B' 
 1  ,  3   , 'DATO C' 
 2  ,  1   , 'DATO D' 
 2  ,  2   , 'DATO E' 
 3  ,  1   , 'DATO G'

and I want to get this result

P_ID,   DATA  ,  DATA_1 ,  DATA_2
 1  , 'DATO A', 'DATO B', 'DATO C'
 2  , 'DATO D', 'DATO E',   NULL   
 3  , 'DATO G',   NULL  ,   NULL

this can be done with a left join with the same table, something like this (not the exact result, but as an example)

select * from
(select * from MYTABLE where PROGR = 1) a
left join 
(select * from MYTABLE where PROGR = 2) b
on a.P_ID = b.P_ID
left join
(select * from MYTABLE where PROGR = 3) c
on a.P_ID = c.P_ID;

The problem is that this query is fixed, and need to be rewritten if some P_ID get PROGR = 4. I think that I need to make a procedure, but I have been trying without success.

Thanks in advance.

Gordon Linoff · Accepted Answer · 2015-10-08 12:34:08Z

3

You can use conditional aggregation:

select t.pid,
       max(case when t.progr = 1 then t.data end) as data_1,
       max(case when t.progr = 2 then t.data end) as data_2,
       max(case when t.progr = 3 then t.data end) as data_3
from mytable t
group by t.pid;

To handle a variable number of columns, I can think of three solutions:

Put in enough columns to handle your data (some reasonable maximum).
Use dynamic SQL (execute immediate in PL/SQL).
Or, combine them into a single column.

Here is the last approach:

select t.pid, listagg(t.data, ', ') within group (order by t.progr)
from mytable t
group by t.pid;

edited Oct 8, 2015 at 12:34

answered Oct 8, 2015 at 12:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin Over a year ago

Thats a good approach, but like the last sentence say, what happen if now I have progr 4 in some row, or then progr 5. The variable number of output columns is not a problem. Thanks for the answer, I have learned something new.

Martin Over a year ago

With dynamic SQL would be the best. Can you give me an example?. Thanks

Tharunkumar Reddy · Accepted Answer · 2015-10-08 12:29:48Z

1

Use below query.

select p_id,max(data_1) as data_1,max(data_2)as data_2,max(data_3) as data_3
    from
    (select P_ID,
    case when progr=1 then 
    data
    end data_1,
    case when progr=2 then 
    data
    end data_2,
    case when progr=3 then 
    data
    end data_3
    from thursday_check)
    group by p_id

answered Oct 8, 2015 at 12:29

Tharunkumar Reddy

2,81320 silver badges33 bronze badges

Collectives™ on Stack Overflow

Multiple rows to one row Oracle SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related