Problem in counting nulls and then merging them wi

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          2Y1,2X1,W2Z

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 Data
 1      W2Z,2X1,3Y1

Please help me out

Thanks in advance

Answer 1

Kind of messy and most likely can be improved

;With RawData AS
(
    select * from @t
)
,Ranked1 as
(
    select *, RANK() OVER (PARTITION BY GroupId, RowID ORDER BY ID, GroupId, RowID) R from @t
)
,Ranked2 as
(
    select *, R - RANK() OVER (PARTITION BY GroupId, RowID ORDER BY ID, GroupId, RowID) R2 from Ranked1
    where Data is null
)
,Ranked3 as
(
    select MIN(ID) as MinID, GroupId, RowID, R2, COUNT(*) C2 from Ranked2
    group by GroupId, RowID, R2
)
,Ranked4 as
(
    select RD.ID, RD.GroupId, RD.RowID, ISNULL(Data, C2) as C3 from RawData RD
    left join Ranked3 R3 on RD.ID = R3.MinID and RD.GroupId = R3.GroupId and RD.RowID = R3.RowID
    where ISNULL(Data, C2) is not null
)
,Grouped as
(
    select GroupId, RowID,
        (
            select isnull(C3, '') from Ranked4 as R41
            where R41.GroupId = R42.GroupId and R41.RowID = R42.RowID
            order by GroupId, RowID for xml path('')
        ) as C4
    from Ranked4 as R42
    group by GroupId, RowID
)
    select GroupId,
        stuff((
            select ',' + C4 from Grouped as G1
            where G1.GroupId = G2.GroupId
            order by GroupId for xml path('')
        ), 1, 1, '')
    from Grouped G2
    group by GroupId