Convert multiple rows into one with comma as separator

[From]: http://stackoverflow.com/questions/887628/convert-multiple-rows-into-one-with-comma-as-separator

If I issue SELECT username FROM Users I get this result:

username
--------
Paul
John
Mary

but what I really need is one row with all the values separated by comma, like this:

Paul, John, Mary

How do I do this?

shareedit

This should work for you. Tested all the way back to SQL 2000.

create table #user (username varchar(25))

insert into #user (username) values ('Paul')
insert into #user (username) values ('John')
insert into #user (username) values ('Mary')

declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + username + ', ' from #user

select SUBSTRING(@tmp, 0, LEN(@tmp))
shareedit

Left join and Left outer join in SQL Server

http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server

What is the difference between left join and left outer join?

shareedit

As per the documentation: FROM (Transact-SQL):

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

The keyword OUTER is marked as optional (enclosed in square brackets), and what this means in this case is that whether you specify it or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will of course make a difference.

For instance, the entire type-part of the JOIN clause is optional, in which case the default is INNER if you just specify JOIN. In other words, this is legal:

SELECT *
FROM A JOIN B ON A.X = B.Y

Here’s a list of equivalent syntaxes:

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

Also take a look at the answer I left on this other SO question: SQL left join vs multiple tables on FROM line?.

enter image description here

shareedit