How to do GROUP_CONCAT in SQL Server?

Create a comma seperated result in SQL Server

• Possible Duplicate: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 http://stackoverflow.com/questions/1621747/concat-field-value-to-string-in-sql-server There are 2 tables involved, Users and Keywords. Users userId firstName lastName ... Keywords userId keyword If I run this query: select u.userId, u.firstName, u.lastName, k.keyword from Users as u inner join Keywords as k on u.userId = k.userId I get something like this: What I want is this: http://www.sqlprof.com/blogs/sqldev/archive/2008/03/31/how-to-list-multiple-rows-of-data-on-same-line.aspx but I can't seem to get it to work right.

• Answer:

  SELECT u.userId, u.firstName, u.lastName, (select keyword +', ' from Keywords tk where tk.userid= u.userid for xml path ('') ) as keyword FROM Users AS u Sample query to test: declare @Users as table( userid int, firstname varchar(20), lastname varchar(20) ) declare @Keywords as table( userid int, keyword varchar(20) ) insert into @Users values( 1, 'Bob','Sanders' ), ( 2, 'Jamie','Smith' ) insert into @Keywords values (1,'Cars'), (1,'Pizza'), (2,'Tomato'), (2,'Blah') SELECT u.userId, u.firstName, u.lastName, (select keyword +', ' from @Keywords tk where tk.userid= u.userid for xml path ('') ) as keyword FROM @Users AS u Returns: userId firstName lastName keyword 1 Bob Sanders Cars, Pizza, 2 Jamie Smith Tomato, Blah,