Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I would like a query to output on 1 line the Id from the left table and the descriptions from a joined table.

Schema:

person
---------    
id (int)
-------------
id (int)
description (varchar(100))
personrole
-------------
personid (int)
roleid (int)
Example data:
person
------------
------------
id   description
1    user
2    admininstrator
3    tester
personrole
-------------
personid   roleid
1          1
2          1
2          2
2          3
So, I'd like the output to be:
PersonId   Roles
1          user
2          user;administrator;tester
╔══════════╦════════════════════════════╗
║ PERSONID ║      DESCRIPTIONLIST       ║
╠══════════╬════════════════════════════╣
║        1 ║ user                       ║
║        2 ║ user;admininstrator;tester ║
╚══════════╩════════════════════════════╝
SQL Server (starting with 2017) supports OOTB STRING_AGG function
select p.id, STRING_AGG(pr.description, ',') as roles 
from person p 
inner join personrole pr ON p.id = pr.personid
inner join roles r ON r.id = pr.roleid
group by p.id
An other SQL example: using GROUP_CONCAT on a single table to group every first name client of a town.
script Sqlite : 
Table:  
CREATE TABLE IF NOT EXISTS 'user'(
        prenom STRING,
        age INTEGER,
        ville STRING);
Data:
INSERT INTO 'user' ('prenom', 'age', 'ville')  VALUES
        ('Anthony', 20, 'Toulouse'),
        ('Clarisse', 18, 'Paris'),
        ('Madeleine', 58, 'Paris'),
        ('Jacques', 45, 'Toulouse'),
        ('Henry', 26, 'Toulouse'),
        ('Lili', 14, 'Nice'),
        ('Victoire', 38, 'Paris');
Normal select :
SELECT * FROM 'user';
OUTPUT :
prenom   age  ville
--------  -- ---------
Anthony   20 Toulouse
Clarisse  18 Paris
Madeleine 58 Paris
Jacques   45 Toulouse
Henry     26 Toulouse
Lili      14 Nice
Victoire  38 Paris
All prenom group by ville : 
SELECT ville, GROUP_CONCAT(prenom, ',')  FROM user GROUP BY ville;
OUTPUT :
ville      liste
--------   ---------
Nice       Lili
Paris      Clarisse,Madeleine,Victoire
Toulouse   Anthony,Jacques,Henry 
                For people like me, just a note to say this is an MySQL answer, the question is MS SQL Server however.
– Paul
                Dec 21, 2015 at 10:33
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.