How to show multiple columns in a single column in SQL?

Question

I have a table attendee, when joining table attendee with table Session, attendee can attend many sessions. So when showing the attendees and what sessions they attended, I am getting multiple rows of the same Attendee, example :

John Doe-BMW Conference
John Doe-Blockchain conference
Jane Doe-blabla
John Doe- Mercedess

Is there a way in sql to display

John Doe- BMW Conference,Mercedes,Block chain?

SELECT Distinct
  attendee.Id, attendee.Firstname, attendee.PhoneNumber,
  attendee.Email,attendee.Town, attendee.BloodType, session.Id ,
  session.LocationId , session.Name as SessionName , location.Id ,
  location.Name as Location_Name , sessionattended.SessionAttendedId,
  sessionattended.SessionId, sessionattended.AttendeeId,
  attendee.Lastname
FROM `session`, `attendee`, `sessionattended`, `location`
WHERE attendee.Id = sessionattended.AttendeeId 
  and session.Id = sessionattended.SessionId 
  and session.LocationId = location.Id;

Why are you choosing not to use proper, explicit, **standard**, readable `JOIN` syntax? — Gordon Linoff, Jun 06 '20 at 13:04
Use `GROUP BY`. And also use modern join syntax available since SQL-92 (28 years ago). — The Impaler, Jun 06 '20 at 13:07
`GROUP BY ` will aggregate multiple rows into a single one. That seems to be what you want. — The Impaler, Jun 06 '20 at 13:09
The answer to this will depend heavily on what DBMS you're using (MySQL, MariaDB, Postgres, Microsoft SQL Server, etc) and what version. Please could you [edit] the question to specify. — IMSoP, Jun 06 '20 at 13:12
Your query has many more columns than your resultset, so your question is rather unclear in that regard. — GMB, Jun 06 '20 at 13:17
use `GROUP_CONCAT` (MySQL) or `STRING_AGG` (MSSQL), see: https://database.guide/mysql-group_concat-vs-t-sql-string_agg/ — Luuk, Jun 06 '20 at 13:20
@Luuk These are what I was referring to when I asked for the DBMS and version. STRING_AGG is only available in very recent SQL Server versions, but has been available for much longer in Postgres, for instance. Also, it's best not to post partial answers in comments, because it makes it hard to judge if the question is answered, and means we can't use features like voting and editing. — IMSoP, Jun 06 '20 at 13:38
@IMSoP: i added this as an answer, but as-long-as the question asker is not complete, in giving info about what SQL he uses .... — Luuk, Jun 06 '20 at 13:52
@Luuk My point was the opposite: if the person writing the question hasn't given enough information, wait for them to do so, rather than guessing and leaving partial answers. The chances are this is actually a duplicate, but we don't know what of yet. And if the clarification never comes, it should be closed, not answered. — IMSoP, Jun 06 '20 at 15:18

score 0 · Answer 1 · answered Jun 06 '20 at 13:51

use GROUP_CONCAT MySQL (available since a long time)

or STRING_AGG MSSQL (available in version 2017 and later)

for a comparison of both, see: https://database.guide/mysql-group_concat-vs-t-sql-string_agg/

Google will find info on how to do this in other DBMS's, or even how to do it 'the old way', like here: GROUP BY to combine/concat a column

score 0 · Answer 2 · answered Jun 06 '20 at 14:20

Try this. Basically the idea is to use FOR XML PATH for this type of result.

SELECT Distinct
       a.Id, a.Firstname, a.PhoneNumber, a.Email,a.Town, a.BloodType, 
       all_conferences = STUFF((SELECT ',' + CAST(s1.Name as Varchar) 
                                FROM sessionattended sa1  
                                JOIN Session s1 ON sa1.SessionId = s1.Id
                                WHERE sa1.AttendeeId = sa.AttendeeId FOR XML PATH('') ), 1, 1, '')

FROM `session` s
JOIN `sessionattended` sa ON sa.SessionId = s.Id
JOIN `attendee` a ON a.Id = sa.AttendeeId 
JOIN `location` l ON s.LocationId = l.Id

This would be a good answer _if_ the question specified MS SQL Server, and a version prior to 2017. There's possibly other scenarios where you need this workaround as well, but for MySQL, newer SQL Server versions, and Postgres, for a start, this won't be the best answer. Unfortunately, the question doesn't actually specify. — IMSoP, Jun 06 '20 at 15:20

How to show multiple columns in a single column in SQL?

2 Answers2