The string equivalent of the sum to concatenate

Question

I would like the query to display on 1 line the identifier from the left table and the description from the joined table.

Scheme:

person --------- id (int) role ------------- id (int) description (varchar(100)) personrole ------------- personid (int) roleid (int) 

Sample data:

 person ------------ id 1 2 role ------------ id description 1 user 2 admininstrator 3 tester personrole ------------- personid roleid 1 1 2 1 2 2 2 3 

So, I would like the result to be:

 PersonId Roles 1 user 2 user;administrator;tester 

Answer 1

 SELECT p.ID PersonID, STUFF( (SELECT ';' + b.description FROM personrole a INNER JOIN role b ON a.roleid = b.id WHERE a.personid = p.id FOR XML PATH ('')) , 1, 1, '') AS DescriptionList FROM person AS p GROUP BY p.ID 

• SQLFiddle Demo

EXIT

 ╔══════════╦════════════════════════════╗ ║ PERSONID ║ DESCRIPTIONLIST ║ ╠══════════╬════════════════════════════╣ ║ 1 ║ user ║ ║ 2 ║ user;admininstrator;tester ║ ╚══════════╩════════════════════════════╝ 

Answer 2

Another SQL example: using GROUP_CONCAT in one table to group each customer name with the first city name.

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 selection:

 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 

The whole 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