Oracle SQL: Transform rows to multiple columns

Question

I'm using Oracle 11G and need a way to turn rows into new groups of columns in a select statement. We're transitioning to a 1:3 relationship for some of our data and need a way to get it into a view. Can you help us transform data that looks like this:

+---------+------------+
| User_Id | Station_Id |  
+---------+------------+  
|       1 |        203 |
|       1 |        204 |
|       2 |        203 |
|       3 |        487 |
|       3 |       3787 |
|       3 |        738 |
+---------+------------+

into this:

+---------+-------------+-------------+---------------+
| User_Id | Station_One | Station_Two | Station_Three |
+---------+-------------+-------------+---------------+
|       1 |         203 | 204         | Null          |
|       2 |         203 | Null        | Null          |
|       3 |         487 | 3787        | 738           |
+---------+-------------+-------------+---------------+

Let me know what ever other specifics you would like and thank you for any help you can give!

void · Accepted Answer · 2015-04-13 21:37:17Z

You can use row_number and self joins:

with cte as 
(
 select userid, stationid,
        row_number() over(partition by userid order by stationid) rn
 from tbl
)

select distinct c1.userid,
                c1.stationid station_one,
                c2.stationid station_two,
                c3.stationid station_three 
from cte c1
left join cte c2 on c1.userid=c2.userid and c2.rn=2
left join cte c3 on c1.userid=c3.userid and c3.rn=3
where c1.rn=1

See the demo

You can also do it with row_number and subqueries:

with cte as 
(
 select userid, stationid,
        row_number() over(partition by userid order by stationid) rn
 from tbl
)

select distinct userid, 
       (select stationid from cte c where c.userid=cte.userid and c.rn=1) station_one,
       (select stationid from cte c where c.userid=cte.userid and c.rn=2) station_two,
       (select stationid from cte c where c.userid=cte.userid and c.rn=3) station_three
from cte

See the demo

David Faber · Accepted Answer · 2015-04-13 22:24:43Z

1

The easiest way to accomplish this in my experience is to use conditional aggregation:

WITH mydata AS (
    SELECT 1 AS user_id, 203 AS station_id FROM dual
     UNION ALL
    SELECT 1 AS user_id, 204 AS station_id FROM dual
     UNION ALL
    SELECT 2 AS user_id, 203 AS station_id FROM dual
     UNION ALL
    SELECT 3 AS user_id, 487 AS station_id FROM dual
     UNION ALL
    SELECT 3 AS user_id, 3787 AS station_id FROM dual
     UNION ALL
    SELECT 3 AS user_id, 738 AS station_id FROM dual
)
SELECT user_id
     , MAX(CASE WHEN rn = 1 THEN station_id END) AS station_one
     , MAX(CASE WHEN rn = 2 THEN station_id END) AS station_two
     , MAX(CASE WHEN rn = 3 THEN station_id END) AS station_three
  FROM (
    SELECT user_id, station_id, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY rownum ) AS rn
      FROM mydata
) GROUP BY user_id;

Just replace the mydata CTE in the above query with whatever your table's name is:

SELECT user_id
     , MAX(CASE WHEN rn = 1 THEN station_id END) AS station_one
     , MAX(CASE WHEN rn = 2 THEN station_id END) AS station_two
     , MAX(CASE WHEN rn = 3 THEN station_id END) AS station_three
  FROM (
    SELECT user_id, station_id, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY rownum ) AS rn
      FROM mytable
) GROUP BY user_id;

answered Apr 13, 2015 at 22:24

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

2 Comments

Ordinary Motorist Over a year ago

Hey David, thanks for your response. Sorry I wasn't more specific in my original question. There are thousands of station IDs so your solution was not dynamic enough for my purpose.

David Faber Over a year ago

I'm not sure I understand what isn't dynamic enough about my answer - it's as dynamic as the one you accepted.

Collectives™ on Stack Overflow

Oracle SQL: Transform rows to multiple columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related