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

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)