I have a table that look like this:
ID Name
-------
1 John
1 Mary
1 Jane
2 John
2 Mary
3 Jane
Knowing that every ID can only contain up to three names, I want to use a SELECT statement to turn this into the following:
ID Name1 Name2 Name3
--------------------
1 John Mary Jane
2 John Mary
3 Jane
Is there a way to do this in SQL?
If you know that there are at most three names, you can do this using conditional aggregation:
select id,
max(case when seqnum = 1 then name end) as name1,
max(case when seqnum = 2 then name end) as name2,
max(case when seqnum = 3 then name end) as name3
from (select t.*, row_number() over (partition by id order by name) as seqnum
from table t
) t
group by id;
With Oracle, you can use the PIVOT feature.
However, you'll need to RANK your rows first, over id-name pairs, and then you can do the pivot directive for (literally) the rank you just generated.
Pages to read:
