Fetch Comma Separated Data using XML Path

Select Qry.*, Case When Qry.Door = '' Then Qry.Door Else Substring(Qry.Door, 3, Len(Qry.Door) - 2) End As Door

From (SELECT dbo.tblHardware.Cmp_Id, Hardware_Id , Hardware_Code AS [Hardware Code],

(Select ', ' + cast(Door_Name as varchar) FROM tbldoor

WHERE tbldoor.Hardware_Id = tblHardware.Hardware_Id FOR XML path('')) Door

FROM dbo.tblHardware WHERE (dbo.tblHardware.Is_Deleted = 0)) As Qry

Pleases refer below site for more detail