5

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.

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
Fishinastorm
  • 51
  • 2
  • What DMBS are you using? – Abe Miessler Aug 26 '10 at 16:40
  • In fact there is a fairly comprehensive set of approaches for tackling this in different RDBMSs here - possible duplicate of [How do I Create a Comma-Separated List using a SQL Query?](http://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query) – Martin Smith Aug 26 '10 at 16:43
  • You should definitely state that upfront in your questions! Looks like you would need [to create a user defined aggregate](http://www.mail-archive.com/derby-user@db.apache.org/msg09451.html) for that. – Martin Smith Aug 26 '10 at 17:14
  • @Martin: Sorry for not including it. Can you please provide an example? I'm not able to understand the content in the link you provided. Thanks. – Fishinastorm Aug 26 '10 at 17:35
  • @Fishina... I don't have SQL derby to play with. If you can find the code for `getMedianTestScore` they talk about in that link and paste it into your question I'll take a look at it. – Martin Smith Aug 26 '10 at 17:50

4 Answers4

5

If you are using MySQL

select Column1, group_concat(Column2)
from t
group by Column1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
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 ;)

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • +1 I think this is the best method in SQL Server there are [other possibilities](http://www.projectdmx.com/tsql/rowconcatenate.aspx) though but not as efficient and don't guarantee ordering. Also you might want to see [here](http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx ) for a way of stopping it escaping special XML characters – Martin Smith Aug 26 '10 at 17:01
  • 1
    Announcer: "And playing shooting guard for the Chicago Bulls we have >&< formerly known as Michael Jordan!!!" (crowd goes wild) – Abe Miessler Aug 26 '10 at 17:05
0

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

fredt
  • 24,044
  • 3
  • 40
  • 61
  • From the comments it turned out that the OP is actually using "java derby" – Martin Smith Aug 27 '10 at 18:31
  • The post didn't mention it. Derby does does not have this capability yet. Therefore the answers may help him choose an alternative if he wants to. – fredt Aug 27 '10 at 23:18
0

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.

Frank
  • 2,628
  • 15
  • 14