行列转换问题

Question

输入

ID  RowID   Data
1   1   S
1   1   Q
1   1   L
1   1   Null
1   1   Null
1   1   S
1   1   E
1   1   R
1   1   V
1   1   E
1   1   R
1   1   Null
1   1   DB
1   2   S
1   2   T
1   2   A
1   2   C
1   2   K
2   1   O
2   1   V
2   1   E
2   1   R
2   1   Null
2   1   Null
2   1   Null
2   1   F
2   1   L
2   1   O
2   1   W

预期输出

ID  NewData
1   SQL2SERVER1DB,STACK
2   OVER3FLOW

Answer 1

保证输出顺序的唯一方法是向表中添加一个定义正确顺序的列 - 我使用了 IDENTITY 列。

我还假设您的示例数据创建脚本中有一个错误（请参阅我对问题的评论） - 修订后的示例数据创建脚本如下：

DECLARE @t TABLE(ID INT IDENTITY(1,1) , groupId  INT, RowID INT, Data VARCHAR(10))
INSERT INTO @t (groupId, RowID,DATA)
SELECT 1,1,'S' UNION ALL SELECT 1,1,'Q' UNION ALL SELECT 1,1,'L' UNION ALL
SELECT 1,1,NULL UNION ALL SELECT 1,1,NULL UNION ALL SELECT 1,1,'S' UNION ALL
SELECT 1,1,'E' UNION ALL SELECT 1,1,'R' UNION ALL SELECT 1,1,'V' UNION ALL SELECT 1,1,'E' UNION ALL
SELECT 1,1,'R' UNION ALL SELECT 1,1,NULL UNION ALL SELECT 1,1,'DB' UNION ALL
SELECT 1,2,'S' UNION ALL SELECT 1,2,'T' UNION ALL SELECT 1,2,'A' UNION ALL SELECT 1,2,'C' UNION ALL
SELECT 1,2,'K' UNION ALL SELECT 2,1,'O' UNION ALL SELECT 2,1,'V' UNION ALL SELECT 2,1,'E' UNION ALL 
SELECT 2,1,'R'  UNION ALL SELECT 2,1,NULL UNION ALL SELECT 2,1,NULL UNION ALL SELECT 2,1,NULL
UNION ALL SELECT 2,1,'F' UNION ALL SELECT 2,1,'L' UNION ALL SELECT 2,1,'O' UNION ALL SELECT 2,1,'W'

据我所知，正确聚合NULL 行并在一个步骤中连接字符串，因此该解决方案使用链式 CTE 一次执行一个步骤。我还使用 CTE 对输出进行重复数据删除（而不是分组）。

--convert consecutive nulls to a count
;WITH nullCTE
AS
(
    SELECT ID, ID AS e
    FROM @t AS t
    WHERE t.Data IS NULL
    AND EXISTS (SELECT 1 FROM @t AS q WHERE q.Data IS NOT NULL AND q.Id = t.ID + 1)

    UNION ALL

    SELECT  c.ID, t.Id
    FROM    @t AS t
    JOIN    nullCTE AS c
    ON      t.ID = c.e - 1
    WHERE t.Data IS NULL
)
-- simplify the null table
,grpCTE
AS
(SELECT ID, ID - e + 1 SIZE,
    ROW_NUMBER() OVER (PARTITION BY ID
                       ORDER BY e
                      ) AS rn
 FROM nullCTE
)
--build the output
,outputCTE
AS
(
    SELECT  t1.groupId,
            (
                SELECT COALESCE(t.Data,CAST(g.size AS VARCHAR(11)),'') + CASE WHEN u.RowID > t.RowID AND u.groupId = t.groupId THEN ',' ELSE '' END
                FROM        @t AS t
                LEFT JOIN   grpCTE  AS g
                ON          g.ID = t.ID
                AND         rn = 1
                LEFT JOIN   @t AS u
                ON          u.ID = t.Id + 1
                WHERE t.groupID = t1.groupId
                ORDER BY t.Id
                FOR XML PATH('') 
            ) AS NewData,
            ROW_NUMBER() OVER (PARTITION BY groupId
                               ORDER BY Id
                              ) AS rn
    FROM @t AS t1
)
SELECT groupId, NewData
FROM outputCTE
WHERE rn = 1