CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Max Szczurek
Max Szczurek

Reputation: 4334

SQL - String concatenated grouping WITHOUT DISTINCT

I'm wondering if this is possible - I have a table like this:

pk int, num int, name varchar(1)
1       1        'a'
2       1        'b'
3       1        'c'
4       1        'd'
5       1        'e'
6       2        'f'
7       2        'g'
8       2        'h'
9       2        'i'
10      2        'j'

And I'd like an output like this WITHOUT using a DISTINCT clause:

num  result
1    a,b,c,d,e
2    f,g,h,i,j

Here are ddl statements for testing:

declare @tbl table (pk int, num int, name varchar(1))
insert into @tbl select 1, 1, 'a'
insert into @tbl select 2, 1, 'b'
insert into @tbl select 3, 1, 'c'
insert into @tbl select 4, 1, 'd'
insert into @tbl select 5, 1, 'e'
insert into @tbl select 6, 2, 'f'
insert into @tbl select 7, 2, 'g'
insert into @tbl select 8, 2, 'h'
insert into @tbl select 9, 2, 'i'
insert into @tbl select 10, 2, 'j'

The following query works, but I'd like to eliminate the DISTINCT clause if possible:

select DISTINCT num, stuff((select ',' + name from @tbl where num = t.num for xml path('')), 1, 1, '')
from @tbl t

Any idea how to do this in SQL 2012+?

Upvotes: 0

Views: 58

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

If you don't have a list of num values that you want, then you can create one. One rather silly way is:

select t.num,
       stuff( (select ',' + name
               from @tbl t2
               where t2.num = t.num
               for xml path('')
              ), 1, 1, '')
from (values (1), (2)) as t(num);

More commonly, this would be written as:

select t.num,
       stuff( (select ',' + name
               from @tbl t2
               where t2.num = t.num
               for xml path('')
              ), 1, 1, '')
from (select distinct num from @tbl) t;

Upvotes: 1

Lokesh Kumar Gaurav
Lokesh Kumar Gaurav

Reputation: 726

Try this I think it will work fine

select num, group_concat(name) from table_name group by num;

Upvotes: 1

Related Questions