oracle - How Can I Flatten the Results of a SQL Query - Transposing Rows to Columns?

Question

I have a table that I want to join some other tables on to. The table called "OfficePers" has a field for the Office Location ID as well as a field for the IDs of people who work in that location and another field with their names. For example, the table is of the following format:

| OfficeLocation | PersonID |
|--------------  | -------- |    
|321             |   2323   |   
|321             |   2355   |   
|321             |   1234   |   
|321             |   7899   |   
|321             |   32091  |   
|321             |   777    |       
|1654            |   4232   |   
|121243          |   345    |       
|121243          |   343    |       
|121243          |   111    |   

What I want to do is create a subquery that returns one result per office location and creates aliases for each personID and name - so the above table would be transformed into something like the following:

| OfficeLocation |  PersonID_1 | PersonID_2 | PersonID_3 | PersonID_4| PersonID_5| PersonID_6|
| -------------- | ----------- |----------- |----------- |-----------|-----------|-----------|
| 321            |      2323   |    2355    |   1234     |  7899     |  32091    |  777      |
| 1654           |      4232   |            |            |           |           |           |
| 121243         |       345   |    343     |    111     |           |           |           |

 

I was thinking of perhaps doing something like just joining the "OfficePers" table on itself multiple times but I'm not sure what function I could use to parse out each Person ID - I'm familiar with using Max and Min but that wouldn't work with a case of having more than 2 Person IDs at the same location.

question from:https://stackoverflow.com/questions/65922108/how-can-i-flatten-the-results-of-a-sql-query-transposing-rows-to-columns

Answer 1

The simplest method is to combine these into a single column using listagg():

select OfficeLocation,
       listagg(personid, ',') within group (order by personid) as personids
from t
group by OfficeLocation;

I do not recommend putting the values in separate columns for several reasons. First, you don't know how many columns you will need. Second, you can do this using dynamic SQL, but you cannot create a view for the result. Third, the same person could -- in theory -- appear in multiple rows, but the person would likely be in different columns.

EDIT:

If you want exactly six columns, you can use conditional aggregation:

select OfficeLocation,
       max(case when seqnum = 1 then PersonId) as PersonId_1,
       max(case when seqnum = 2 then PersonId) as PersonId_2,
       max(case when seqnum = 3 then PersonId) as PersonId_3,
       max(case when seqnum = 4 then PersonId) as PersonId_4,
       max(case when seqnum = 5 then PersonId) as PersonId_5,
       max(case when seqnum = 6 then PersonId) as PersonId_6
from (select t.*,
             row_number() over (partition by OfficeLocation order by personid) as seqnum
      from t
     ) t
group by OfficeLocation;