concatenate multiple rows into one row in sql server

Question

I have following table PreventMaintainance ID Date 1 2018-12-14 2 2018-12-16 PreventMaintainanceDetail ID Value 1 9874125 1 8745216 1 7251489 I want following struture 1 9874125,8745216,7251489 I am using following query,where I am getting Incorrect syntax near the keyword 'as'..How I will resolve this?? Select parts=STUFF( (SELECT ',' + t2.Value FROM PreventMaintain t1 inner join PreventMaintainanceDetail t2 on t1.Id = t2.Id FOR XML PATH ('') ) , 1, 1, '') as value,pm.Date from dbo.PreventMaintain pm inner join dbo.PreventMaintainanceDetail pd on pm.Id=pd.Id

Suraj Kumar · Answer

For date you can try this below query. You can also check live demo here https://rextester.com/WBLPN65530 SELECT temp .Id, dateCol, temp .Value FROM ( SELECT id, value = STUFF(( SELECT ', ' + Convert ( Varchar (10),value) FROM PreventMaintainanceDetail b where b.id = a.id FOR XML PATH( '' )), 1, 2, '' ) FROM PreventMaintainanceDetail a GROUP BY id ) temp inner join PreventMaintainance on temp .Id = PreventMaintainance.Id Now the output will be as shown below Id Value dateCol 1 9874125, 8745216, 7251489 2018-12-14

Priyanka Kumari · Answer

@Suraj Kumar I need to select value from PreventiveMaintainance like date,so in query provided by you,where i will select date from PreventiveMaintainance table. Plz help me out

Suraj Kumar · Answer

You can try this create table PreventMaintainanceDetail (id int , value int ) insert into PreventMaintainanceDetail values (1, 9874125), (1, 8745216), (1, 7251489) SELECT id, value = STUFF(( SELECT ', ' + Convert ( Varchar (10),value) FROM PreventMaintainanceDetail b WHERE b.id = a.id FOR XML PATH( '' )), 1, 2, '' ) FROM PreventMaintainanceDetail a GROUP BY id The output is as shown below id value 1 9874125, 8745216, 7251489

Madan Shekar · Answer

select STUFF((SELECT distinct ', ' + CONVERT(varchar(500), value)from PreventMaintainanceDetail where id=PreventMaintainance.id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,2,'') as PreventMaintainanceDetailValue from PreventMaintainance

concatenate multiple rows into one row in sql server

Answers (4)