Rows to comma separated list in SQL

Go To


I have a requirement wherein the table structure I have looks like

ID                 Owner Id                NAME  
1                  20                      Name 1  
1                  21                      Name 2  
1                  34                      Name 3  
2                  10                      Name 4  
2                  12                      Name 5  
3                  100                     Name 6  

I need a query that would give me the results as

ID                Owner ID                Name  
1                  20                     Name 1, Name2, Name 3  
2                  10                     Name4, Name5  
3                  100                    Name 6  

Currently we do this on the codebehind, but I would ideally like to do this through SQL and see if that amounts to any performance improvement.

2012-04-04 17:42
by Vish
How are you deciding which Owner Id to show - RedFilter 2012-04-04 17:42
You'll have to specify the database for this question. there's no generic solutio - Conrad Frix 2012-04-04 17:43
@RedFilter looks like just the min one - Matt Ball 2012-04-04 17:43
If you have code that currently takes data from SQL and produces the output you want, I suggest seeing why that code is so slow. Making SQL do extra work other than simply returning a dataset is likely not going to fix your performance problems - datagod 2012-04-04 18:05


You did not mention your DBMS so I'm assuming PostgreSQL:

       min(owner_id) as lowest_id,
       string_agg(name, ', ') as name_list
FROM the_table
2012-04-04 17:44
by a_horse_with_no_name
Its the first Owner ID and the DBMS is SQL - Vish 2012-04-04 17:54
Sorry guys forgot to mention I cannot use min here. The Darn DB ids are strings and its something like 'TNX-0000-CFT-0001'. It irks me when the primary key is a string but then thats how the system has been running for the last 10 years - Vish 2012-04-04 17:57
@user1313368: "SQL" is not a DBMS, it's a language. And min() can be used on a string value - a_horse_with_no_name 2012-04-04 18:05