How can I group by a column and also list all of the results from another column

https://www.codecademy.com/paths/data-science/tracks/sql-intermediate/modules/dspath-sql-multiple-tables/lessons/sql-multiple-tables-code-challenge/exercises/inner-join-ii

so I am working on prt 3 of the link . The directions say, “Let’s see the titles of songs that were played by each user!” The directions say how to do it however it lists the user_id and not the actually person’s name.
I wanted to see if I could have it list the first_name, last_name, and every title of each song they have listened to.

My current code --------------
WITH chart1 AS (
SELECT *
FROM plays
JOIN users
ON users.id = plays.user_id
)
SELECT first_name, last_name, title
FROM chart1
JOIN songs
ON chart1.song_id = songs.id
ORDER BY first_name

returns
fname | lname | song1
fname | lname | song2
fname | lname | song3
fname2 | lname2 | song1
fname2 | lname2 | song2
fname2 | lname2 | song3

how can I make it return?
fname | lname | song1, song2, song3
fname2 | lname2 | song1, song2, song3

edit: well I found the GROUP_CONCAT() function online and it helps “SELECT GROUP_CONCAT(titles) AS …” however the data is all in 1 column now and I would prefer it to be song1 | song2 | song3|
I think that would make it a bit easier to see the most played song and do other functions later.

There’s no easy way to do a pivot table like that in SQLite. You can in SQL Server and other larger-scale RDBMSs, but in my opinion it’s generally more trouble than it’s worth in SQL. If you’re just looking to find the most played songs by each person, you could just add a column that counts the title, then group by first_name, last_name and title

Click for example code
WITH chart1 AS (
  SELECT p.user_id, p.song_id, s.title, u.first_name, u.last_name
  FROM plays p
  JOIN users u
    ON u.id = p.user_id
  JOIN songs s
    ON p.song_id = s.id
)
SELECT first_name, last_name, title, COUNT(title)
FROM chart1
GROUP BY 1, 2, 3
ORDER BY 3;

Alternatively, if you’re just looking for the most played song (regardless of user) you just select title and COUNT(title) and group by title.

Click for example code
WITH chart1 AS (
  SELECT p.user_id, p.song_id, s.title, u.first_name, u.last_name
  FROM plays p
  JOIN users u
    ON u.id = p.user_id
  JOIN songs s
    ON p.song_id = s.id
)
SELECT title, COUNT(title)
FROM chart1
GROUP BY 1
ORDER BY 2 DESC;
2 Likes