How can I 'flatten' a one to many table with URLs so that each additional URL shows up in a new column?

Question

I'm trying to 'flatten' a one to many relationship using SQL to create a CSV of points and their associated photos to use with a web map.

Table 1 is a list of points and their locations, and Table 2 is a list of URLs of photos and their associated captions.

Table 1
+-------------+------------+-----------+-------------+
| LOCATION_ID |    Name    | Latitude  |  Longitude  |
+-------------+------------+-----------+-------------+
|           1 | Dawson     |     64.06 | -139.410833 |
|           2 | Whitehorse | 60.716667 |     -135.05 |
+-------------+------------+-----------+-------------+

Table 2
+-------------+-------------------------+----------------------+
| LOCATION_ID |         CAPTION         |         URL          |
+-------------+-------------------------+----------------------+
|           1 | Photo of Dawson city    | http://fakeurl.com/1 |
|           1 | Photo of Klondike River | http://fakeurl.com/2 |
|           1 | Photo of Yukon River    | http://fakeurl.com/3 |
|           2 | Photo of Main Street    | http://fakeurl.com/4 |
|           2 | Photo of Miles Canyon   | http://fakeurl.com/5 |
+-------------+-------------------------+----------------------+

How do I write SQL code so that it creates a table that looks like this?

+-------------+------------+-----------+-------------+----------------------+----------------------+-------------------------+----------------------+----------------------+----------------------+
| LOCATION_ID |    NAME    | Latitude  |  Longitude  |       CAPTION1       |         URL1         |        CAPTION2         |         URL2         |       CAPTION3       |         URL3         |
+-------------+------------+-----------+-------------+----------------------+----------------------+-------------------------+----------------------+----------------------+----------------------+
|           1 | Dawson     |     64.06 | -139.410833 | Photo of Dawson city | http://fakeurl.com/1 | Photo of Klondike River | http://fakeurl.com/2 | Photo of Yukon River | http://fakeurl.com/3 |
|           2 | Whitehorse | 60.716667 |     -135.05 | Photo of Main Street | http://fakeurl.com/4 | Photo of Miles Canyon   | http://fakeurl.com/5 |                      |                      |
+-------------+------------+-----------+-------------+----------------------+----------------------+-------------------------+----------------------+----------------------+----------------------+

It's quite hard to do in SQL, especially with a variable number of columns. And the question is whether you should, because it's probably equally hard to process such a CSV, depending of course what you want to use it for. — GolezTrol, May 14 '19 at 19:29

Gordon Linoff · Accepted Answer · 2019-05-14T21:05:10.583

You want to pivot the data in table2. But to do so, you need a pivoting column, which can be generated using row_number().

I prefer to use conditional aggregation for pivoting, so:

select t1.LOCATION_ID, t1.Name, t1.Latitude, t1.Longitude,
       max(case when seqnum = 1 then t2.caption end) as caption_1,
       max(case when seqnum = 1 then t2.url end) as url_1,
       max(case when seqnum = 2 then t2.caption end) as caption_2,
       max(case when seqnum = 2 then t2.url end) as url_2,
       max(case when seqnum = 3 then t2.caption end) as caption_3,
       max(case when seqnum = 3 then t2.url end) as url_3
from table1 t1 left join
     (select t2.*,
             row_number() over (partition by location_id order by location_id) as seqnum
      from table2 t2
     ) t2
     on t1.location_id = t2.location_id
group by t1.LOCATION_ID, t1.Name, t1.Latitude, t1.Longitude;

that works great! Thank you very much. You have a small typo in the line "(partition by location_id order *by* location_id)". — Btelliot, May 14 '19 at 20:29

How can I 'flatten' a one to many table with URLs so that each additional URL shows up in a new column?

1 Answers1