multiple column with muptiple rows data to a single row & for the non duplicated data need concadination

Question

SQL Query:-
declare @t table
(
    Id int,
    ReportedDate DATE,
    [Name] varchar(10)
)
insert into @t

select 1,'2016-01-01','ab' union all
select 2,'2016-01-01','a' union all
select 1,'2016-01-20','hha' union all
select 2,'2016-01-20','jnsjja' union all
select 1,'2016-01-01','jsjb' union all
select 2,'2016-01-01','sjjjwb' union all
select 1,'2016-01-20','bjd' union all
select 2,'2016-01-20','bwjw'

select * from @t order by id, ReportedDate

Expected Result:-

It's working in case we have only 2 columns, one is Id & other one is anything. But for mutiple column I am unable

Looks like this is your answer...[Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Sean Lange, Mar 31 '17 at 21:31
As I mentioned It's working in case we have only 2 columns, but for mutiple columns I am unable. For 2 columns I have query select t.Id, ( select [Name] + ',' from @t where Id = t.Id for XML path('') ) from (select Id from @t )t — Rakesh Pattanayak, Mar 31 '17 at 21:44

score 0 · Accepted Answer · answered Mar 31 '17 at 21:42

There are countless examples for this, however, since you have a nicely structured question which is easy to copy and paste....

Select A.ID 
      ,A.ReportedDate
      ,Name  = Stuff((Select Distinct ',' +Name From @t Where ID=A.ID and ReportedDate=A.ReportedDate For XML Path ('')),1,1,'') 
 From (Select Distinct ID,ReportedDate From @t ) A

Returns

ID  ReportedDate    Name
1   2016-01-01      ab,jsjb
1   2016-01-20      bjd,hha
2   2016-01-01      a,sjjjwb
2   2016-01-20      bwjw,jnsjja

multiple column with muptiple rows data to a single row & for the non duplicated data need concadination

1 Answers1