SQL - How to Concatenate Values?

Dr_Evil · 13 Mar 2007 at 17:05

Hi,

i want to return a list of concatenated string values into 1 string, from a number of rows in my table.

e.g. i have a table with ID and code, such as:

ID Code
1 AA
2 AB
3 AC

and I want to write 1 SQL statement that returns 1 string value: "AA, AB, AC".

can I do this, without having to write a stored procedure?

happytechie · 13 Mar 2007 at 17:12

Oracle

select ID || code FROM table;

SQL Server

select ID + code from table;

I think MySQl uses || as well but I'm not certain...

Dr_Evil · 13 Mar 2007 at 17:17

happytechie said:
Oracle

select ID || code FROM table;

SQL Server

select ID + code from table;

I think MySQl uses || as well but I'm not certain...

That's not what I'm after, it won't work as ID needs to be casted into a varchar type first anyway, but the result will still be a number of rows with values. I want 1 value returned, consisting of a concatenation of all Codes, separated by ","

Haircut · 13 Mar 2007 at 17:24

What database?

I know in Oracle it is possible to write your own custom aggregate function that could do something like this.
Can't remember how to do it though unfortunately.

I guess the other option would be to write a stored proc to do this.

EDIT: This is how you do custom aggregate functions in Oracle
http://download-east.oracle.com/docs/cd/B14117_01/appdev.101/b10800/dciaggfns.htm

rpstewart · 13 Mar 2007 at 17:32

Dr_Evil said:
and I want to write 1 SQL statement that returns 1 string value: "AA, AB, AC".

What you're basically looking to do is a pivot table but with a variable number of rows/columns. I wouldn't want to attempt that in pure SQL, it's going to be easier to include some kind of procedural programming round it - either in a script or a stored procedure.

happytechie · 13 Mar 2007 at 17:33

sorry I misunderstood your question, for any multi row stuff you'll need to go into a procedural type language. What database are you on it'd be trivial in T/SQL or PL/SQL

Dr_Evil · 13 Mar 2007 at 17:34

SQL Server 2005 - User-defined aggregate function sounds like it can do the job, i'll look into that. Any other ideas?

Haircut · 13 Mar 2007 at 17:40

Dr_Evil said:
SQL Server 2005 - User-defined aggregate function sounds like it can do the job, i'll look into that. Any other ideas?

I have a feeling SQL Server 2005 can do custom aggregate functions as well but I don't know SQL Server!
I'm sure I read something about being able to do custom aggregate functions in C# in Visual Systems Journal though

EDIT:
A quick google shows this:
http://msdn2.microsoft.com/en-us/library/ms131056.aspx

Looks like pretty much what you are after

matja · 13 Mar 2007 at 18:27

this is how i've always done it...

Code:

mysql> select * from mytable;
+------+------+
| id   | code |
+------+------+
|    1 | aa   |
|    2 | ab   |
|    3 | ac   |
+------+------+

mysql> select group_concat(code) from mytable;
+--------------------+
| group_concat(code) |
+--------------------+
| aa,ab,ac           |
+--------------------+

Dr_Evil · 19 Mar 2007 at 09:59

matja said:
this is how i've always done it...

Yes but that's mySQL, i'm using SQL Server.

I created a CRL Aggregate Function in C#, compiled it into a DLL, registered this in SQL Server and now I can use this in my queries. Thanks for all the help.

(p.s. if I had VS2005 Developer, i could have done this directly by making a database project and creating a custom aggregate function, this would compile directly and register with SQL Server i believe..)

matja · 19 Mar 2007 at 10:16

Dr_Evil said:
Yes but that's mySQL, i'm using SQL Server.

sux 2 b u

Dr_Evil · 19 Mar 2007 at 10:30

matja said:
sux 2 b u

Dude I choose .Net and SQL server over PHP/MySQL anytime!!

matja · 19 Mar 2007 at 10:35

Dr_Evil said:
Dude I choose .Net and SQL server over PHP/MySQL anytime!!

So how long did it take you to concatenate those results, in the end?

Dr_Evil · 19 Mar 2007 at 10:46

matja said:
So how long did it take you to concatenate those results, in the end?

1-2 hours, but what about all the hours i've won by using .Net components, where you had to fully code them in PHP ?!?

matja · 19 Mar 2007 at 11:15

where did PHP come into this? :eek:

Dr_Evil · 19 Mar 2007 at 11:19

What other reason would you have for using mySQL ?

Mr^B · 19 Mar 2007 at 15:37

It's free? Has many features which are missing from MSSQL (group_concat and limit for starters)

ASP (.Net or otherwise) + MySQL is a pretty good combination for small applications.

pinkaardvark · 19 Mar 2007 at 16:42

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html