Merge (Combine) common rows with comma separated string using STUFF function in SQL Server

Last Reply 5 months ago By dharmendr

Posted 5 months ago

Stuff function in SQL.

I have a record of table name table_log

select t.Id,t.UserId,t.RowNumber,t.Sheet,t.SupplyType,t.FileType,t.CreatedDate,t.CreatedBy,t.ModifiedDate,t.ModifiedBy,
t.tablename,isnull(s.TableName,'Master Upload Pending') as Mastername from Table_Log t left join Table_Log s on t.Id=s.ParentId where t.ParentId=0

now data is coming 

its showing the correct data but i want to show comma seprated value like data3,data4 come in single row for id 1

Id	UserId	RowNumber	Sheet	SupplyType	FileType	CreatedDate	CreatedBy	ModifiedDate	ModifiedBy	tablename	Mastername
1	1	1	        Sheet01	SupplyType01	File01	         2019-12-12	1	        2019-12-12	1	         data1	          Data3
1	1	1	        Sheet01	SupplyType01	File01	         2019-12-12	1	        2019-12-12	1	         data1	          Data4
2	1	2	        Sheet01	SupplyType01	File01	         2019-12-12	1	        2019-12-12	1	         data2	          Master Upload Pending
3	1	1	        Sheet01	SupplyType01	File01	         2019-12-12	1	        2019-12-12	1	         VenderReport	  Master Upload Pending

 

 

Posted 5 months ago Modified on 5 months ago

Hi telldurges,

Refer below query.

SQL

DECLARE @Test AS TABLE
(
	Id INT,UserId INT,RowNumber INT,Sheet VARCHAR(10),SupplyType VARCHAR(20),
	FileType VARCHAR(10),CreatedDate DATETIME,CreatedBy INT,
	ModifiedDate DATETIME,ModifiedBy INT,tablename VARCHAR(50),Mastername VARCHAR(50)
)
INSERT INTO @Test VALUES(1,1,1,'Sheet01','SupplyType01','File01','2019-12-12',1,'2019-12-12',1,'data1','Data3')
INSERT INTO @Test VALUES(1,1,1,'Sheet01','SupplyType01','File01','2019-12-12',1,'2019-12-12',1,'data1','Data4')
INSERT INTO @Test VALUES(2,1,2,'Sheet01','SupplyType01','File01','2019-12-12',1,'2019-12-12',1,'data2','Master Upload Pending')
INSERT INTO @Test VALUES(3,1,1,'Sheet01','SupplyType01','File01','2019-12-12',1,'2019-12-12',1,'VenderReport','Master Upload Pending')

;WITH CTE AS
(
	SELECT * FROM @Test
	-- Your Query to get result from table.
)
SELECT DISTINCT Id,UserId,RowNumber,Sheet,SupplyType,FileType,CreatedDate,CreatedBy,ModifiedDate,ModifiedBy,tablename,
STUFF((	SELECT ',' + Mastername 
		FROM CTE t2
		WHERE t1.Id = t2.Id
		FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS Mastername
FROM CTE t1

Output

Id	UserId	RowNumber	Sheet	SupplyType	FileType	CreatedDate	CreatedBy	ModifiedDate	ModifiedBy	tablename	Mastername
1	1	1	Sheet01	SupplyType01	File01	2019-12-12 00:00:00.000	1	2019-12-12 00:00:00.000	1	            data1	    Data3,Data4
2	1	2	Sheet01	SupplyType01	File01	2019-12-12 00:00:00.000	1	2019-12-12 00:00:00.000	1	            data2	    Master Upload Pending
3	1	1	Sheet01	SupplyType01	File01	2019-12-12 00:00:00.000	1	2019-12-12 00:00:00.000	1	            VenderReport    Master Upload Pending