(SQL) Getting a column for every ID out of a junction table-CodePudding

[If this was already answered elsewhere, I will thankfully take the link and delete this]

I have a playlists table:

pid | name 

1   | playlist 1 
2   | playlist 2
3   | playlist 3

and a songs table:

sid | name 

1   | song 1 
2   | song 2
3   | song 3

they are connection with this junction table:

pid | sid 

1   | 1 
1   | 2
1   | 3
2   | 2 
3   | 1

I already managed to get the join to work

SELECT      playlists.pid, songs.*
FROM        playlists
INNER JOIN  playlist_contains_song
ON          playlists.pid = playlist_contains_song.pid
INNER JOIN  songs
ON          playlist_contains_song.sid = songs.sid;

which gave me this result:

pid | sid | name 

1   | 1   | song 1    
1   | 2   | song 2
1   | 3   | song 3    
2   | 2   | song 2    
3   | 1   | song 1

Now I wondered if there's a way to get this data 'horizontally'. The preferred output should look like this:

pid | 1 | 2 | 3

1   | 1 | 1 | 1
2   | 0 | 1 | 0
3   | 1 | 0 | 0

Where the columns are the sid's and the values stand for true or false, whether the song is part of the playlist or not.

Thanks in advance!

CodePudding user response：

Use a pivot query:

SELECT pid,
       COUNT(*) FILTER (WHERE sid = 1) AS "1",
       COUNT(*) FILTER (WHERE sid = 2) AS "2",
       COUNT(*) FILTER (WHERE sid = 3) AS "3"
FROM playlist_contains_song
GROUP BY pid
ORDER BY pid;