Results as comma separated

Many times we needed to have some values as comma separated , the following are few scenarios.

Single Table

--Creating table
Create table TableX(id int  ,name char(10))

--Inserting table
Insert into tablex valueS(1,'a'),(1,'b'),(1,'c'),(2,'d'),(2,'e'),(3,'f'),(4,'G'),(4,'H')

--Query
SELECT ID,  Stuff((SELECT ', ' + name
FROM   TableX t2 where t1.id=t2.id GROUP  BY id,name
FOR XML PATH('')), 1, 1, '') CSV
FROM   TableX t1
GROUP  BY id

Following shall be output

Having two table and needed joins

--CREATE AND INSERT RECORDS
CREATE TABLE TABLE1(ID INT PRIMARY KEY ,NAME CHAR(10))
INSERT INTO TABLE1 VALUES(1,'TEST'),(2,'TEST2')

CREATE TABLE TABLE2(ID INT REFERENCES TABLE1(ID),SERVICES CHAR(10))
INSERT INTO TABLE2 VALUES(1,'SERVICE1'),(1,'SERVICE2'),(1,'SERVICE3'),
        (1,'SERVICE4'),(1,'SERVICE5'),
        (2,'SERVICE1'),(2,'SERVICE2')

--QUERY
SELECT MAX(T1.ID) AS 'COL1',T3.NAME AS 'COL2', STUFF((SELECT ', ' + SERVICES
FROM   TABLE2 T2 WHERE T1.ID=T2.ID GROUP  BY ID,SERVICES
FOR XML PATH('')), 1, 1, '') AS 'CSV'
FROM   TABLE2 T1
INNER JOIN TABLE1 T3 ON T1.ID=T3.ID
GROUP  BY T1.ID,T3.NAME

Following shall be output

The following is other way of doing without STUFF function and with Substring. Data selected from a single table (Table2)

SELECT TOP 10 x1.ID
	,CSVList = SUBSTRING((
			SELECT (',' + SERVICES)
			FROM table2 x2
			WHERE x1.ID = x2.ID
			ORDER BY ID
				,SERVICES
			FOR XML Path('')
			), 2, 1000)
FROM table2 x1
GROUP BY x1.ID

Thanks
Manish