suppose there is a table entrepreneur in a database holding names, with multiple rows like below:-
For Example:-
Company_Id entrepreneur_NameName
———- ————————————-
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
According to our requirement we will write a SQL Query which will produce expected output like below.
in SQL server Query to Concatenate text from multiple rows into a single string
-------------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
---------------Finally Concatenate text from multiple rows into a single string in SQL server------------
SELECT DISTINCT EP2.Company_Id,
SUBSTRING((SELECT ','+EP1. entrepreneur_Name AS [text()]
FROM dbo.entrepreneur EP1
WHERE EP1.Company_Id = EP2.Company_Id
ORDER BY EP1.Company_Id
FOR XML PATH ('')
), 2, 1000) [entrepreneur_name]
FROM dbo.entrepreneur EP2
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
