Concatenating records in a single column without looping?

Question

I have a table with 1 column of varchar values. I am looking for a way to concatenate those values into a single value without a loop, if possible. If a loop is the most efficient way of going about this, then I'll go that way but figured I'd ask for other options before defaulting to that method. I'd also like to keep this inside of a SQL query.

Ultimately, I want to do the opposite of a split function.

Is it possible to do without a loop (or cursor) or should I just use a loop to make this happen?

Edit: Since there was a very good answer associated with how to do it in MySql (as opposed to MS Sql like I initially intended), I decided to retag so others may be able to find the answer as well.

Answer 1

declare @concat varchar(max) set @concat = ''

select @concat = @concat + col1 + ',' from tablename1

Answer 2

I just tackled a problem like this and looping took forever. So, I concantenated the values in the presentation medium (in this case Crystal Reports) and it was very fast.

Just an idea.

Answer 3

Probably dated now but check out Adam Machanic's post on the topic:

http://is.gd/4rvwm

And this one is certainly dated; I wrote it in 2004:

http://is.gd/4rvy6

Why do I prefer a function over "keeping it inside a SQL query"? Because you'll probably have to do this more than once. Why not encapsulate that code into a single module instead of repeating it all over the place?

Answer 4

If it is MySQL, you can use GROUP_CONCAT

SELECT a, GROUP_CONCAT(b SEPARATOR ',') FROM table GROUP BY a;

Answer 5

try this:

DECLARE @YourTable table (Col1 int)
INSERT INTO @YourTable VALUES (1)
INSERT INTO @YourTable VALUES (2)
INSERT INTO @YourTable VALUES (30)
INSERT INTO @YourTable VALUES (400)
INSERT INTO @YourTable VALUES (12)
INSERT INTO @YourTable VALUES (46454)

SELECT
    STUFF(
             (
                  SELECT
                      ', ' + cast(Col1 as varchar(30))
                      FROM @YourTable
                      WHERE Col1<=400
                      ORDER BY Col1
                      FOR XML PATH('')
             ), 1, 2, ''
         )

OUTPUT:

-------------------
1, 2, 12, 30, 400

(1 row(s) affected)