GROUP BY but get all values from other column
SqlGroup BySql Problem Overview
I''ll explain what I need to do on example. First of all, we have a simple table like this one, named table:
id | name
===+=====
1 | foo
1 | bar
1 | foobar
2 | foo
2 | bar
2 | foobar
Now the query:
SELECT t.* FROM table t GROUP BY t.id
Will get us result similar to this one:
id | name
===+=====
1 | foo
2 | foo
But is it possible, to collect all values of name to have result like this?
id | name
===+=================
1 | foo, bar, foobar
2 | foo, bar, foobar
Sql Solutions
Solution 1 - Sql
Using MySQL you can use GROUP_CONCAT(expr)
> This function returns a string result with the concatenated non-NULL > values from a group. It returns NULL if there are no non-NULL values. > The full syntax is as follows:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
Something like
SELECT ID, GROUP_CONCAT(name) GroupedName
FROM Table1
GROUP BY ID
SQL Fiddle DEMO
Solution 2 - Sql
For SQL Server (before 2017) use FOR XML
clause and STUFF()
function for that:
SELECT distinct id, name =
STUFF((SELECT ' , ' + name
FROM Table1 b
WHERE b.id = a.id
FOR XML PATH('')), 1, 2, '')
FROM Table1 a
GROUP BY id;
###UPDATE
With SQL Server 2017, you can simply use STRING_AGG()
function to achieve that:
SELECT ID, STRING_AGG (name, ', ') AS Name
FROM Table1
GROUP BY ID
###See this SQLFiddle
Solution 3 - Sql
For Postgres use string_agg()
select id,
string_agg(name, ',' order by name) as name_list
from the_table
group by id
order by id;
Solution 4 - Sql
Below query works for you if you are using db2:
SELECT DISTINCT id, LISTAGG(names, ',')
FROM tb
GROUP BY id;