XML Path in SQL Server

XML PATH AND STUFF FOR for String Concatenation in SQL Server

We can use XmlPath('') to concatenate column data into single row.

Stuff is used to remove the first ‘,’ after string concatenation.

STEP 1:- Create a Table named ‘CITY’

CREATE TABLE CITY (CT_ID Int, CT_Name Varchar(20))

STEP 2:- Insert Data in ‘CITY’ Table

INSERT INTO CITY

SELECT 1,'KOLKATA' UNION ALL SELECT 1,'TARAKESWAR' UNION ALL

SELECT 2,'MUMBAI' UNION ALL SELECT 3,'DELHI' UNION ALL

SELECT 4,'CHENNAI' UNION ALL SELECT 5,'BENGALURU' UNION ALL

SELECT 6,'GOA' UNION ALL SELECT 2,'PUNJAB' UNION ALL

SELECT 3,'SHIMLA' UNION ALL SELECT 4,'CHANDIGARH' UNION ALL

SELECT 5,'AHMEDABAD' UNION ALL SELECT 6,'IMPHAL' UNION ALL

SELECT 1,'DUMDUM' UNION ALL SELECT 2,'JAIPUR' UNION ALL

SELECT 3,'VIZAG' UNION ALL SELECT 4,'HYDERABAD' UNION ALL

SELECT 5,'RAIPUR' UNION ALL SELECT 6,'GUWAHATI'

STEP 3:- SELECT * FROM CITY

STEP 4:- Now we use STUFF() and XML Path('') Function in SQL Query.

SELECT DISTINCT CT_ID,

STUFF(( SELECT ',' + CT_NAME

            FROM CITY T1

            WHERE T1.CT_ID = T2.CT_ID

       FOR XML PATH('')),1,1,'')

            FROM CITY T2

Thank You! J