short the data on multiple line to single line

[Senthilkumar]

Dear team,

I am filtering the data from my database. My query is 

Select distinct P.Number, P.Description, M.[Name] from SubAssembly as SA
inner join PartDrawing as PD
on SA.PartDrawingID = PD.Id
inner join CatalogPartDrawing  as CPD
on PD.Id = CPD.PartDrawingID
inner join [Catalog] as C
on C.ID = CPD.CatalogID
inner join Model as M
on M.ID = C.ModelID
inner join Part as P
on SA.ChildPartID = P.ID and P.Number in(80001619)

My outpiut is 

 

But i Want the output like belo image,

 

Can any one pls correct me to get my required output.

[Barand]

Use

GROUP_CONCAT(M.Name SEPARATOR ', ') as name 

and

GROUP BY P.Number

 

[Senthilkumar]

Dear Barand,

Using the GROUP_CONCAT I am getting error

Pls look on it

[Barand]

Check your SQL reference manual for correct syntax (I'm used to mysql - I haven't used MSSQL since 2010) although I wouldn't be surprised if MSSQL's pathetic function library doesn't contain it.

It's fairly simple to do it in PHP though. Create an array of names for each number the join() them.

PS Looks like the mssql equivalent is STRING_AGG()

[Senthilkumar]

Dear Barand,

 

Thanks for your guid. I changed the code as per below

Select distinct P.Number, P.Description,STRING_AGG( CAST(M.Name as nvarchar(MAX)),', ') as name  from SubAssembly as SA
inner join PartDrawing as PD
on SA.PartDrawingID = PD.Id
inner join CatalogPartDrawing  as CPD
on PD.Id = CPD.PartDrawingID
inner join [Catalog] as C
on C.ID = CPD.CatalogID
inner join Model as M
on M.ID = C.ModelID
inner join Part as P
on SA.ChildPartID = P.ID and P.Number in(80001619) GROUP BY P.Number, P.Description

I am getting output now.