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
0
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
0
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
0
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
-1