How to group and concatenate query results

Question

Say I have a query like this:

Select 
    dept, 
    person_id
form 
    depts

which returns the following result

dept              person_id
-----             ---------
'sales'              2
'management'         2
'sales'              3
'administrative'     4
'management'         4

How can I make a query that returns the following result ?

 depts                         person_id
 -----                         ---------
'sales, management'             2
'sales'                         3
'administrative, management'    4

mySQL supports group_concat [example](http://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql) which would do this. Look for an equivalent in your RDBMS if not mySQL — xQbert, Jun 25 '15 at 13:59
Actually I would prefer a inter-dbms solution, that's why I didn't specify. But for this query I'm using Sql Server. But I would like to do this in PostgreSQL also. — Nelson Teixeira, Jun 25 '15 at 13:59
ListAgg may work depending on version of [PostgreSQL](http://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent), but [MSFT uses for xml path](http://stackoverflow.com/questions/15477743/listagg-in-sqlserver) I don't think you'll find common functionality between both to do this unless you write a similar UDF for both. nice article [here](http://www.postgresonline.com/journal/archives/191-String-Aggregation-in-PostgreSQL,-SQL-Server,-and-MySQL.html) — xQbert, Jun 25 '15 at 14:01

Giorgi Nakeuri · Accepted Answer · 2015-06-25T14:40:13.900

1

Not for PostgreSql but will work for Ms Sql Server:

select person_id,
       (select stuff((select ', ' + dept from TableName t2
        where t2.person_id = t1.person_id
        for xml path('')), 1, 2, '')) as depts 
from TableName t1
group by person_id

EDIT:

;with cte as(your super big query here)

select person_id,
       (select stuff((select ', ' + dept from cte t2
        where t2.person_id = t1.person_id
        for xml path('')), 1, 2, '')) as depts 
from cte t1
group by person_id

edited Jun 25 '15 at 14:40

answered Jun 25 '15 at 14:01

Giorgi Nakeuri

35,155
8
47
75

Giorgi, I'm wondering, what would be executed faster in this case, the group by or a select distinct? – Jeremy C. Jun 25 '15 at 14:09
@JeremyC., there are plenty of topic with discussions. In many cases it will be the same, but unless you check the execution plan no one can tell wich is better. – Giorgi Nakeuri Jun 25 '15 at 14:12
Alright thanks, I had this query but with distinct and then I saw yours, tested it and found out it produces the same result :p was just wondering if either was best practice but if it's basically the same I won't look too far into it – Jeremy C. Jun 25 '15 at 14:13
@JeremyC., unfortunately I think this is exactly the case to use group by. See this article and check execution plan of both version to ensure: http://blogs.msmvps.com/robfarley/2007/03/24/group-by-v-distinct-group-by-wins/ – Giorgi Nakeuri Jun 25 '15 at 14:18
thanks, it worked great. but actually this is a simplification of the real problem. the real table is actually a very complex query with several subqueries. So I have to replicate the whole query 2 times for your solution. I'm not an sql expert, but I know it has something as a declare function that you can apply to queries. Is there a way of not having to repeat the whole query ? – Nelson Teixeira Jun 25 '15 at 14:36
1

@NelsonTeixeira, yes, you can use cte - common table expressions. Please edits for that... – Giorgi Nakeuri Jun 25 '15 at 14:38
Again it worked great thanks. I would upvote you again if it was possible :) Somebody please do this for me. I took a little while to get it to work because I didn't put the initial ";" why is it necessary ? – Nelson Teixeira Jun 25 '15 at 14:57
@NelsonTeixeira, it is not the initial. It is the ending of previous statement. Because `with` is a common keyword, it will produce an ambiguity. For example you can use with keyword like `select * from someTable with cte as (....)`. Here actually are 2 statements but what did you mean? `select * from someTable with(nolock)` and forgot `nolock` or you mean `select * from someTable; with cte as (....)`. Did you get? I wrote `;with` just to terminate prev statement: `select * from someTable ;with cte as (....)` – Giorgi Nakeuri Jun 25 '15 at 15:05

Suchit kumar · Answer 2 · 2015-06-25T14:31:56.220

1

you can try this:

SELECT group_concat(dept),person_id FROM depts group by person_id ;//mysql

edited Jun 25 '15 at 14:31

answered Jun 25 '15 at 14:01

Suchit kumar

11,809
3
22
44

shouldn't that be group_concat? I think concat function takes 2 parameters – Jeremy C. Jun 25 '15 at 14:07

How to group and concatenate query results

2 Answers2