Sql select query help

Question

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 ;)