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
