How to merge rows into single column using FOR XML PATH in sql?

Question

I want to merge rows into single column using FOR XML PATH in SQL.

Here is my Query:

select d.Device from tbl_Sales_OrderItems tsoi left join tbl_devices d 
on d.DeviceID=tsoi.DeviceID
where salesorderid=102 
and tsoi.Quantity>0
and tsoi.TypeID=1

union all

select d.Partnumber Device from tbl_Sales_OrderItems tsoi left join tbl_VendorParts d 
on d.VendorPartID=tsoi.RefID
where salesorderid=102 
and tsoi.Quantity>0
and tsoi.TypeID=2

Here i am getting two rows i.e Two Device name in from tbl_Sales_Order.

Now these rows i want to get in single row using FOR XML PATH into a single column.

Further after applying FOR XML PATH i want to use it in select query i want to use the row value which would be as follows.

Select salesorderid,@resultinRow from tbl_Sales_Orders

Output Required:

SalesOrderID  Devices
  102          Device1,Device2
  103          Device3,Device2

Answer 1

Test Data

DECLARE @TABLE TABLE (SalesOrderID INT, Devices VARCHAR(30))
INSERT INTO @TABLE VALUES
(102,'Device1'),
(102,'Device2'),
(103,'Device3'),
(103,'Device2')

Query

SELECT t.SalesOrderID
      , STUFF(( SELECT ', ' + Devices
                FROM @TABLE 
                WHERE SalesOrderID = t.SalesOrderID
                FOR XML PATH(''),TYPE)
                .value('.','NVARCHAR(MAX)'),1,2,'') AS Devices
FROM @TABLE t
GROUP BY t.SalesOrderID

Result Set

╔══════════════╦══════════════════╗
║ SalesOrderID ║     Devices      ║
╠══════════════╬══════════════════╣
║          102 ║ Device1, Device2 ║
║          103 ║ Device3, Device2 ║
╚══════════════╩══════════════════╝