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

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

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

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