question

**Input:** ID groupId RowID Data 1 1 1 W 2 1 1 NULL 3 1 1 NULL 4 1 1 Z 5 1 2 NULL 6 1 2 NULL 7 1 2 X 8 1 2 NULL 9 1 3 NULL 10 1 3 NULL 11 1 3 Y 12 1 3 NULL **Expected Output** GroupId NewData 1 W2Z,2X1,2Y1 For every Null there will be a numeric count. That is if there are two nulls then the numeric value will be 2. The ddl is as under DECLARE @t TABLE(ID INT IDENTITY(1,1) , GroupId INT, RowID INT, Data VARCHAR(10)) INSERT INTO @t (GroupId, RowID,DATA) SELECT 1,1,'W' UNION ALL SELECT 1,1,NULL UNION ALL SELECT 1,1,NULL UNION ALL SELECT 1,1,'Z' UNION ALL SELECT 1,2,NULL UNION ALL SELECT 1,2,NULL UNION ALL SELECT 1,2,'X' UNION ALL SELECT 1,2,NULL UNION ALL SELECT 1,3,NULL UNION ALL SELECT 1,3,NULL UNION ALL SELECT 1,3,'Y' UNION ALL SELECT 1,3,NULL select * from @t My version is as under but not the correct output ;with t as ( select GroupID, id, RowID, convert(varchar(25), case when Data is null then '' else Data end) Val, case when Data is null then 1 else 0 end NullCount from @t where id = 1 union all select t.GroupID, a.id,a.RowID, convert(varchar(25), Val + case when Data is not null or (t.RowID <> a.RowID and NullCount > 0) then ltrim(NullCount) else '' end + case when t.RowID <> a.RowID then ',' else '' end + isnull(Data, '')), case when Data is null then NullCount + 1 else 0 end NullCount from t inner join @t a on t.GroupID = a.GroupID and t.id + 1 = a.id ) select GroupID, Data = Val + case when NullCount > 0 then ltrim(NullCount) else '' end from t where id = (select max(id) from @t where GroupID = t.GroupId) Is yielding the below output GroupId NewData 1 W2Z,2X1,3Y1 Please help me out Thanks in advance