CODEWITHSUNDEEP
sql-server-2005
Tripati Subudhi
Tripati Subudhi

Reputation: 1691

How to show multiple row values in comma separated in a single row in SQL Server 2005?

Below I have shown two tables and also the result table.

enter image description here

How can I get the result table in this manner as I shown on above?

Upvotes: 0

Views: 2997

Answers (3)

Marek Grzenkowicz
Marek Grzenkowicz

Reputation: 17333

You can define a CLR user-defined aggregate to do it. I posted a detailed description of such a solution as an answer to another question - TSQL Comma Separation. There you'll also find a link to a blog post that discusses the problems you may encounter while developing a CLR aggregate.

After you deploy the custom aggregate to the server (I named the function Concat but yours may be named differently), you will be able to obtain the required result with the following query:

SELECT Val, dbo.Concat(Cat)
FROM Table2
GROUP BY Val

Upvotes: 0

SP007
SP007

Reputation: 1921

DECLARE @Table1 TABLE
( 
 id INT
 ,Val VARCHAR(100)
)
DECLARE @Table2 TABLE
(
id INT
,Val VARCHAR(100)
,Cat VARCHAR(100)
)

INSERT INTO @Table1
VALUES(1,'XYZ')

INSERT INTO @Table1
VALUES(2,'abc')

INSERT INTO @Table2
VALUES(1,'XYZ','a')

INSERT INTO @Table2
VALUES(1,'abc','e')

INSERT INTO @Table2
VALUES(1,'XYZ','b')

INSERT INTO @Table2
VALUES(1,'XYZ','f')

INSERT INTO @Table2
VALUES(1,'abc','g')


SELECT t1.id,t1.Val ,( SELECT STUFF((SELECT ',' + cat  FROM @Table2 t2 WHERE  t2.Val =                   t1.val FOR XML PATH('')),1,1,''))
 FROM   @Table1 t1

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

select min(ID) as ID,
       Val,
       stuff((select ','+Cat
              from Table2 as T2
              where T1.Val = T2.Val
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 1, '') as Cat
from Table2 as T1
group by Val
order by ID

SQL Fiddle

Upvotes: 6

Related Questions