create table [temp](
[id] [nvarchar](10) not null,
[name] [nvarchar](50) not null,
[info1] [nvarchar](50) not null,
[info2] [nvarchar](50) not null,
[info3] [nvarchar](50) not null);
insert into temp(id,name,info1,info2,info3) values ('id1','name1','infoa','infob','infoc');
insert into temp(id,name,info1,info2,info3) values ('id1','name1','infox','infod','infoc');
insert into temp(id,name,info1,info2,info3) values ('id1','name1','infoz','infob','infoc');
Table looks as follow
temp table
id name info1 info2 info3
id1 name1 infoa infob infoc
id1 name1 infox infod infoc
id1 name1 infoy infob infoc
multiple rows from the temp table will be grouped by id, name and all unique info columns will be concatenated expected output
id name info1 info2 info3
id1 name1 infoa;infox;infoy infob;infod infoc
select [id],[name],
stuff((select distinct ',' + CAST(t2.[info1] as varchar(10))
from [temp] t2 where t1.id = t2.id and t1.name = t2.name
for xml path('')),1,1,'') info1,
stuff((select distinct ',' + CAST(t3.[info2] as varchar(10))
from [temp] t3 where t1.id = t3.id and t1.name = t3.name
for xml path('')),1,1,'') info2,
stuff((select distinct ',' + CAST(t4.[info3] as varchar(10))
from [temp] t4 where t1.id = t4.id and t1.name = t4.name
for xml path('')),1,1,'') info3
from [temp] t1
group by id,Name
Function Used
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments