Sql select query help

Question

Hi all, I have a database table with data as shown below:

Primary key | Column1       | Column2          | col3 | col4 |col5
---------------------------------------------------------------------

1           | Chicago Bulls | Michael Jordan   | 6'6  | aaaa | cccc

2           | Chicago Bulls | Scottie Pippen   | 6'8  | zzzz | 345

3           | Utah Jazz     | Malone           | 6'9  | vvvv | xcxc

4           | Orlando Magic | Hardaway         | 6'7  | xnnn | sdsd

I want to write a query which will fetch all distinct values in Column1 and append values in Column2 for each Column1 value. For eg: The query should return

**Chicago Bulls | Michael Jordan, Scottie Pippen**

**Utah Jazz     | Malone**

**Orlando Magic | Hardaway**

I can write a query to fetch all distinct values in column1, then iterate over each distinct value to get the appended column 2 after some manipulation. Is it possible to do the entire job in only one query? Please help with an example of a query. Thanks.

Answer 1

If you are using MySQL

select Column1, group_concat(Column2)
from t
group by Column1     

Answer 2

If you are using SQL Server:

SELECT Column1,
stuff((
    SELECT ', ' + Column2
    FROM tableName as t1
    where t1.Column1 = t2.Column1
    FOR XML PATH('')
    ), 1, 2, '')
FROM tableName as t2
GROUP BY Column1

Not sure why Microsoft makes this one so hard, but as far as I know this is the only method to do this in SQL Server...

On a side note you might consider changing Column1 to a lookup table or the next time Utah moves you're going to be hating life ;)

Answer 3

The general solution to this type of problem is the aggregate function ARRAY_AGG() that returns an array containing the values in different rows, optionally ordered by some criteria. This function has been proposed for the next version of the SQL Standard. The GROUP_CONCAT() function is a special case that converts the array to a comma separated string.

Both ARRAY_AGG() and GROUP_CONCAT() are supported by the latest HSQLDB 2.0.1. http://hsqldb.org/doc/2.0/guide/dataaccess-chapt.html#N12312

Answer 4

SQL Anywhere has a list() aggregation function since more than ten years for exactly this purpose.

If you are using SQL Server, then apart from the tricky solution that abuses XML to some extent, you can write your own aggregation function in any .net language. And the Microsoft documentation of this feature uses exactly the case of string concatenation as an example.