Concatenate many rows into one string

Question

I'm using SQL Server 2008 R2 Enterprise 64 bit.

My question is:

I have two tables t1 and t2 one has an Id column the other a name column

I can query them so that I get the following result:

ID     Name
1      bob
1      ted
2      bill
3      frank

What I want is the result to look like this:

ID     name
1      bob ted
2      bill
3      frank

The name column can have 1 to n names

What I really need to do is pivot the second column. I have used the cast for xml and stuff functions but these dont really work for what I need.

I tried to write a pivot function but I get an error message.

SELECT ID, name As name   
FROM

(SELECT   ID, name 
 FROM dbo.t1 AS t1 
             INNER JOIN
             dbo.t2 AS t2 ON t1.ID = t2.ID                         
             WHERE  (some filter)
AS s
PIVOT
(
  max(name)
  FOR
  [name]
  IN ( [name]
)) AS p

Thanks in advance :)

Maybe you could come up with a better title for your question.... — rory.ap, Jun 23 '15 at 12:11
Your title is already available at google as search result. Do you really want that people will find an answer to their _common question_: "Hi im using MSSQL server 2008 R2 enterprise 64 bit"? — Tim Schmelter, Jun 23 '15 at 12:13
declare @t table (ID INT, Name VARCHAR(10)) INSERT INTO @t (ID,name)values (1,'bob'),(1,'ted'),(2,'bill'),(3,'frank') select ID, stuff( ( select ','+ [Name] from @t where Id = t.Id for XML path('') ),1,1,'') from (select distinct ID from @t )t — mohan111, Jun 23 '15 at 12:20

score 3 · Answer 1 · answered Jun 23 '15 at 12:22

One method is that you can concatenate the second table via a subquery with FOR XML PATH.

DECLARE @t1 TABLE (ID INT)
INSERT @t1 VALUES (1),(2),(3)
DECLARE @t2 TABLE (ID INT, Name VARCHAR(50))
INSERT @t2 VALUES (1,'bob'),(1,'ted'),(2,'bill'),(3,'frank')

SELECT
    T1.ID,
    LTRIM((SELECT ' ' + Name AS [text()] FROM @t2 WHERE ID = T1.ID FOR XML PATH(''))) AS name
FROM @t1 T1
-- WHERE (filter here)

Here are the results from the sample:

ID          name
----------- --------------------
1           bob ted
2           bill
3           frank

@craigrobinson, Please feel free to upvote an answer if it helped you and accept it if it solved your question. This helps others to know which answers may have helped and if any solved your question. It also increases your reputation. — Jason W, Jul 09 '15 at 01:49

score 1 · Answer 2 · answered Jun 23 '15 at 12:27

declare @t table (ID INT,    Name VARCHAR(10))
INSERT INTO @t (ID,name)values (1,'bob'),(1,'ted'),(2,'bill'),(3,'frank')

declare @tt table (ID INT)
INSERT INTO @tT (ID)values (1),(1),(2),(3)
Select P.ID,P.Name FRom (
select T.ID,stuff(
(
    select ','+ [Name] from @t  where Id = t.Id for XML path('')
),1,1,'')Name
from @t  t 
JOIN @tt tt on t.ID = tt.ID
group by T.ID,T.Name )P
GROUP BY P.ID,P.Name

Concatenate many rows into one string

2 Answers2