concatenate multiple rows into one row in sql server

Priyanka Kumari

7y1kLearn iOS Programming

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar7y ago
Accepted 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 Kumari7y ago
@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 Kumar7y ago
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 Shekar7y ago
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