sql - 如何跨行组合 SQL 中的二进制 block ？

Question

我们需要连接 SQL Server 2014 数据库中的二进制数据 block 。我们对此的要求是由于通过 WAN 链接复制 varbinary(max) 数据的速度较慢，如此处所述 - Slow merge replication over a WAN link - only downloads所以我们必须将 varbinary(max) 列减少到更小的宽度。

给定一个表格和一些虚拟数据:

DECLARE @testTable TABLE
(
    [ItemIdx] int,
    [ChunkIdx] int,
    [BinaryData] varbinary(8000)
)
INSERT INTO @testTable ([ItemIdx], [ChunkIdx], [BinaryData]) VALUES (1, 1, 0x00112233), (1, 3, 0x44556677), (1, 2, 0x8899AABB)
INSERT INTO @testTable ([ItemIdx], [ChunkIdx], [BinaryData]) VALUES (2, 1, 0xFFEEDDCC), (2, 3, 0xBBAA9988), (2, 2, 0x77665544)

我可以编写 T-SQL 来连接与单个 ItemIdx 对应的行的数据:

DECLARE @concatData varbinary(max)
SELECT @concatData = COALESCE(@concatData, 0x) + BinaryData FROM @testTable
WHERE ItemIdx = 1
ORDER BY ChunkIdx

结果为 0x001122338899AABB44556677，这是单个 ItemIdx 的预期结果。

有没有办法以一种简洁的方式连接所有 ItemIdx 条目？我想要的结果是这样的:

ItemIdx | Data
1       | 0x001122338899AABB44556677
2       | 0xFFEEDDCC77665544BBAA9988

CLR 将是一个非常明智的选择，尽管缺乏对表值参数的支持使得我能想到的任何方法都很尴尬。我曾多次尝试使用 GROUP BY、COALESCE、FOR XML、CTE 和其他一些方法，但没有成功。

Answer 1

您可以使用 FOR XML 加入它:

DECLARE @ItemIdx INT = 1;

SELECT CONVERT(VARBINARY(MAX),
    (
     SELECT CONVERT(VARCHAR(MAX), BinaryData,2) AS [text()]
     FROM @testTable
     WHERE ItemIdx = @ItemIdx
     ORDER BY ChunkIdx
     FOR XML Path('')
    ),2)

LiveDemo

<小时/>

对于所有行，您可以使用:

SELECT DISTINCT ItemIdx, CONVERT(VARBINARY(MAX),
       (
         SELECT CONVERT(VARCHAR(MAX), BinaryData,2) AS [text()]
         FROM @testTable t2
         WHERE t1.ItemIdx = t2.ItemIdx
         ORDER BY ChunkIdx
         FOR XML Path('')
       ),2) AS Data
FROM @testTable t1;

LiveDemo2

并以 varchar 形式进行最终检查:

LiveDemo3

输出:

╔═════════╦════════════════════════════╗
║ ItemIdx ║            Data            ║
╠═════════╬════════════════════════════╣
║       1 ║ 0x001122338899AABB44556677 ║
║       2 ║ 0xFFEEDDCC77665544BBAA9988 ║
╚═════════╩════════════════════════════╝