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 | | | +-------------+------------+-----------+-------------+----------------------+----------------------+-------------------------+----------------------+----------------------+----------------------+
Advertisement
Answer
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;