Oracle SQL: how to merge n lines and create additional columns in result?

Question

Table:

id race
1  elf
1  troll
2  lizard
2  elf
2  human    
3  dwarf

I am looking for a request that output this:

id race1   race2   race3
1  elf     troll   
2  lizard  elf     human
3  dwarf

There can be n race or a given max number of races if it's more easy

Is this possible with an sql query (not pl/sql)? (oracle if special function is needed)

A SQL query has a fixed number of defined columns. You can only do what you want with dynamic SQL (`execute immediate`). — Gordon Linoff, Feb 05 '19 at 02:49
Possible duplicate of [Dynamic pivot in oracle sql - Procedure](https://stackoverflow.com/questions/42009404/dynamic-pivot-in-oracle-sql-procedure) — Kaushik Nayak, Feb 05 '19 at 03:02
@GordonLinoff ok so let's say with maximum 5 races, it could be possible ? — Tyvain, Feb 05 '19 at 03:08
@KaushikNayak I am not looking for pl/sql but for a sql request. I will make it more clear in the question — Tyvain, Feb 05 '19 at 03:09
If it's unknown number of columns, then pure sql solution won't be possible unless you want an XML output. With fixed columns you can — Kaushik Nayak, Feb 05 '19 at 03:10

score 1 · Accepted Answer · answered Feb 05 '19 at 03:10

You can use conditional aggregation if you want to do this in a simple select:

select id,
       max(case when seqnum = 1 then race end) as race_1,
       max(case when seqnum = 2 then race end) as race_2,
       max(case when seqnum = 3 then race end) as race_3,
       max(case when seqnum = 4 then race end) as race_4,
       max(case when seqnum = 5 then race end) as race_5
from (select t.*,
             row_number() over (partition by id order by id) as seqnum
      from t
     ) t
group by id;

Oracle SQL: how to merge n lines and create additional columns in result?

1 Answers1