Duplicate id rows with few columns to unique id row with many columns Oracle SQL

Question

I have a pole table that can have one to four streetlights on it. Each row has a pole ID and the type (a description) of streetlight. I need the ID's to be unique with a column for each of the possible streetlights. The type/description can anyone of 26 strings.

I have something like this:

ID   Description
----------------
1    S 400
1    M 200
1    HPS 1000
1    S 400
2    M 400
2    S 250
3    S 300

What I need:

ID   Description_1   Description_2   Description_3   Description_4
------------------------------------------------------------------
1    S 400           M 200           HPS 1000        S 400
2    M 400           S 250
3    S 300

The order the descriptions get populated in the description columns is not important, e.g. for ID = 1 the HPS 1000 value could be in description column 1, 2, 3, or 4. So, long as all values are present.

I tried to pivot it but I don't think that is the right tool.

select * from table t
pivot (
max(Description) for ID in (1, 2, 3))

Because there are ~3000 IDs I would end up with a table that is ~3001 rows wide...

I also looked at this Oracle SQL Cross Tab Query But it is not quite the same situation.

What is the right way to solve this problem?

Do you know the maximum number of columns you want to generate? — Gordon Linoff, Jun 25 '20 at 19:39
Why s 400 repeated twice?. What happens if same description repeats 100 times? — , Jun 25 '20 at 19:43
@Sam you can have many streetlights of the same type on one pole. There are 26 types of streetlights. So, values will be repeated often. — fallingdog, Jun 25 '20 at 19:57
@GordonLinoff It would be the ID plus the 4 possible descriptions so 5 total. — fallingdog, Jun 25 '20 at 20:00

score 1 · Accepted Answer · answered Jun 25 '20 at 19:44

You can use row_number() and conditional aggregation:

select 
    id,
    max(case when rn = 1 then description end) description_1,
    max(case when rn = 2 then description end) description_2,
    max(case when rn = 3 then description end) description_3,
    max(case when rn = 4 then description end) description_4
from (
    select t.*, row_number() over(partition by id order by description) rn
    from mytable t
) t
group by id

This handles up to 4 descriptions per id. To handle more, you can just expand the select clause with more conditional max()s.

Duplicate id rows with few columns to unique id row with many columns Oracle SQL

1 Answers1