STUFF AND FOR XML PATH in SQL Server :-

We can use XmlPath(”) to concatenate multiple column data into single row && Stuff is used to remove the first ‘,’ after string concatenation.

suppose there is a table entrepreneur in a database holding names, with multiple rows like below:-

For Example:-

Company_Id entrepreneur_Name

———- ————————————-

1 Sundar

1 Larry

1 Sergey

2 Steave

2 Tim

3 Bill

3 Sathya

Desired output should be like below:-

Company_Id entrepreneur_Name

———- —————————-

1 Sundar, Larry, Sergey

2 Steave, Tim

3 Bill, Sathya

Concatenate text from multiple rows into a single string in SQL server Using STUFF AND FOR XML PATH

-------------Create a Table with Name entrepreneur create table entrepreneur ( Company_Id int, entrepreneur_Name varchar(25) ) ------------Check The Table Structure exec sp_columns 'entrepreneur' -----------Inserting Some records in entrepreneur Table insert into entrepreneur values(1,'Sundar') insert into entrepreneur values(1,'Larry') insert into entrepreneur values(1,'Sergey') insert into entrepreneur values(2,'Steave') insert into entrepreneur values(2,'Tim') insert into entrepreneur values(3,'Bill') insert into entrepreneur values(3,'Sathya') ---------------Check the records in entrepreneur table select * from entrepreneur ---------------Check the records in entrepreneur table select * from entrepreneur ---------------Finally Concatenate text from multiple rows into a single string in SQL server------------ select distinct Company_Id , STUFF((Select ','+entrepreneur_Name from entrepreneur e1 where e1.Company_Id=e2.Company_Id FOR XML PATH('')),1,1,'') [entrepreneur_name] from entrepreneur e2

After executing of above script you will get the output like below:-

Company_Id entrepreneur_name

1 Sundar,Larry,Sergey

2 Steave,Tim

3 Bill,Sathya