计算空值然后将其与现有行合并时出现问题 [英] Problem in counting nulls and then merging them with the existing rows

问题描述

输入：

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

预期输出

GroupId NewData
1          2Y1,2X1,W2Z

每个Null都会有一个数字计数。也就是说，如果有两个空值，则数值将为2。

For every Null there will be a numeric count. That is if there are two nulls then the numeric value will be 2.

ddl如下

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)

正在产生以下输出

GroupID Data
 1      W2Z,2X1,3Y1

请帮帮我

预先感谢

推荐答案

有点混乱，很可能会得到改善

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

这篇关于计算空值然后将其与现有行合并时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！