Create a comma seperated result in SQL Server

Question

Possible Duplicate:
Simulating group_concat MySQL function in MS SQL Server 2005?
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:

enter image description here

What I want is this:

enter image description here

This looked very similar to what I wanted but I can't seem to get it to work right.

score 1 · Answer 1 · answered Nov 09 '11 at 17:43

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,

score 1 · Answer 2 · answered Nov 09 '11 at 17:52

;WITH a AS (
  SELECT 
    u.userId,
    u.firstName,
    u.lastName,
    ( SELECT k.keyword + ',' 
      FROM Keywords k
      WHERE k.userId = u.userId
      ORDER BY k.keyword
      FOR XML PATH('') 
    ) AS [keyword]
  FROM Users u
)
SELECT 
  a.userId,
  a.firstName,
  a.lastName,
  -- strip off trailing comma
  LEFT(
    a.keyword, 
    LEN(a.keyword)-1
  ) AS keyword
FROM a

score 0 · Answer 3 · answered Nov 09 '11 at 17:50

You can create a new sql aggregate function concat to concatenate texts and use if in you query:

select userId, firstName, Lastname, concat(Keyword)
from Users
group by userId, firstName, Lastname;

Follwoing link shows an example on how to create the concat funcion. http://msdn.microsoft.com/en-us/library/ms182741.aspx

Create a comma seperated result in SQL Server

3 Answers3