SQL - How to Concatenate Values?

[Dr_Evil]

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?

 

[Dr_Evil]

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 ","

 

[Dr_Evil]

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

 

[Dr_Evil]

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

 

[Dr_Evil]

sux 2 b u

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

 

[Dr_Evil]

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 ?!?

 

[Dr_Evil]

What other reason would you have for using mySQL ?