Merge multiple rows into one row in a SQL Server

CREATE TABLE ProductsDemo
(
ID INT PRIMARY KEY,
Sno VARCHAR(100),
ProdType VARCHAR(100),
ProdQuantity Int
)
GO


INSERT INTO ProductsDemo
VALUES
(1, '1', 'Dell',2),
(2, '1', 'Lenovo',3) ,
(3, '1', 'Nokia',1),
(4, '1', 'Apple',4),
(5, '1', 'Samsung',1) ,
(6, '2', 'Microsoft',2) ,
(7, '2', 'Logitech',3)
GO

select * from ProductsDemo

 --Dynamic output
SELECT
    Sno,
    STUFF((SELECT CAST(',' AS varchar(max)) + ProdType
            FROM ProductsDemo  as b
            WHERE b.Sno = a.Sno
            FOR XML PATH(''), TYPE
            ).value('.', 'varchar(max)'
         ),1, 1,'') AS ProdType
FROM
    (SELECT DISTINCT Sno FROM ProductsDemo ) AS a;
GO

----Update only ProdType

--UPDATE ProductsDemo SET
-- ProdType=
-- STUFF((SELECT CAST(',' AS varchar(max)) + ProdType
--            FROM ProductsDemo  as b
--            WHERE  ID = 1 or ID=2 or ID=3 or ID=4 or ID= 5
--            FOR XML PATH(''), TYPE
--            ).value('.', 'varchar(max)'
--         ),1, 1,'')
-- WHERE ID = 1

--select * from ProductsDemo

--UPDATE ProductsDemo SET ProdType=
-- STUFF((SELECT CAST(',' AS varchar(max)) + ProdType
--            FROM ProductsDemo  as b
--            WHERE  ID = 6 or ID=7
--            FOR XML PATH(''), TYPE
--            ).value('.', 'varchar(max)'
--         ),1, 1,'')
-- WHERE ID = 6

--select * from ProductsDemo
---------------------------

--Update ProdType With quantity
select * from ProductsDemo
 --Dynamic output
SELECT
    Sno,
    STUFF((SELECT CAST(',' AS varchar(max)) + ProdType
            FROM ProductsDemo  as b
            WHERE b.Sno = a.Sno
            FOR XML PATH(''), TYPE
            ).value('.', 'varchar(max)'
         ),1, 1,'') AS ProdType,
(SELECT  Sum(c.ProdQuantity) from ProductsDemo c
WHERE c.Sno = a.Sno) as RollupQ
FROM
    (SELECT DISTINCT Sno FROM ProductsDemo ) AS a;
GO
select * from ProductsDemo

--Update actual table for ProdType and quantity
UPDATE ProductsDemo SET
ProdType=
 STUFF((SELECT CAST(',' AS varchar(max)) + ProdType
            FROM ProductsDemo  as b
            WHERE  ID = 1 or ID=2 or ID=3 or ID=4 or ID= 5
            FOR XML PATH(''), TYPE
            ).value('.', 'varchar(max)'
         ),1, 1,''),
ProdQuantity=(SELECT  Sum(c.ProdQuantity) from ProductsDemo c
WHERE ID = 1 or ID=2 or ID=3 or ID=4 or ID= 5)
WHERE ID = 1

 select * from ProductsDemo

UPDATE ProductsDemo SET ProdType=
 STUFF((SELECT CAST(',' AS varchar(max)) + ProdType
            FROM ProductsDemo  as b
            WHERE  ID = 6 or ID=7
            FOR XML PATH(''), TYPE
            ).value('.', 'varchar(max)'
         ),1, 1,''),
ProdQuantity=(SELECT  Sum(c.ProdQuantity) from ProductsDemo c
WHERE ID = 6 or ID=7 )
WHERE ID = 6


select * from ProductsDemo

delete from ProductsDemo where ID=7
delete from ProductsDemo where ID=2 or ID=3 or ID=4 or ID=5
select * from ProductsDemo

--delete ProductsDemo
--Drop table ProductsDemo

Note: Problem with above update query, it will merge duplicate ProdTypes, if u want to handle duplicate ProdTypes then change the query.