sql-server - SQL Serve - 如何将 n 行分组到 xml 路径的子节点

Question

declare @textline table (lineid int, textline varchar(100))

insert Into @textline (lineid, textline)
values (1, 'AAAA BBBB CCCC DDDD'),
       (2, '1212 3434 5656 7878'),
       (3, 'ABCD DEFG GHIJ JKLM'),
       (4, 'AAAA BBBB CCCC DDDD'),
       (5, '1212 3434 5656 7878'),
       (6, 'ABCD DEFG GHIJ JKLM')

Select TextLine Line
  From @textline       
   For Xml Path(''), Root('TextBlock')

-- The result as 
<TextBlock>
  <Line>AAAA BBBB CCCC DDDD</Line>
  <Line>1212 3434 5656 7878</Line>
  <Line>ABCD DEFG GHIJ JKLM</Line>
  <Line>AAAA BBBB CCCC DDDD</Line>
  <Line>1212 3434 5656 7878</Line>
  <Line>ABCD DEFG GHIJ JKLM</Line>
</TextBlock>

如何为这个预期结果修改TSQL代码(SQL Server)？

我们要对 n 行(本例为 3 行)进行分组，预期结果如下:

<TextBlock>
  <Line>AAAA BBBB CCCC DDDD</Line>
  <Line>1212 3434 5656 7878</Line>
  <Line>ABCD DEFG GHIJ JKLM</Line>
</TextBlock>
<TextBlock>
  <Line>AAAA BBBB CCCC DDDD</Line>
  <Line>1212 3434 5656 7878</Line>
  <Line>ABCD DEFG GHIJ JKLM</Line>
</TextBlock>

Answer 1

可以使用整数除法作为分组排名

您的测试场景(更改最后数字以标记结果行)

declare @textline table (lineid int, textline varchar(100))

insert Into @textline (lineid, textline)
values (1, 'AAAA BBBB CCCC 1111'),
       (2, '1212 3434 5656 1122'),
       (3, 'ABCD DEFG GHIJ 1133'),
       (4, 'AAAA BBBB CCCC 2211'),
       (5, '1212 3434 5656 2222'),
       (6, 'ABCD DEFG GHIJ 2233');

--CTE给结果集增加一个分组排名

WITH Numbered AS 
(
    SELECT tl.lineid
          ,tl.textline
          ,(ROW_NUMBER() OVER(ORDER BY tl.lineid)-1)/3 AS GroupRank
    FROM @textline AS tl
)
--Each grouping rank is one row, which returns all related lines 
--The XML parts are put together with the outer `FOR XML`
SELECT (
        SELECT innerTL.textline AS Line
        FROM Numbered AS innerTL
        WHERE innerTL.GroupRank=Numbered.GroupRank
        FOR XML PATH(''),TYPE
       )
FROM Numbered
GROUP BY GroupRank
FOR XML PATH('TextBlock');

结果

<TextBlock>
  <Line>AAAA BBBB CCCC 1111</Line>
  <Line>1212 3434 5656 1122</Line>
  <Line>ABCD DEFG GHIJ 1133</Line>
</TextBlock>
<TextBlock>
  <Line>AAAA BBBB CCCC 2211</Line>
  <Line>1212 3434 5656 2222</Line>
  <Line>ABCD DEFG GHIJ 2233</Line>
</TextBlock>