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
STUFF AND FOR XML PATH