Q&A about the site for people who love being outdoors enjoying nature and wilderness, and learning about the required skills and equipment
DECLARE @testTable TABLE ( [ItemIdx] int, [ChunkIdx] int, [BinaryData] varbinary(8000) ) INSERT INTO @testTable ([ItemIdx], [ChunkIdx], [BinaryData]) VALUES (1, 1, 0x313233), (1, 3, 0x313233), (1, 2, 0x313233); INSERT INTO @testTable ([ItemIdx], [ChunkIdx], [BinaryData]) VALUES (2, 1, 0x313233), (2, 3, 0x313233), (2, 2, 0x313233); /*SELECT * FROM @testTable;*/ /*SELECT CONVERT(VARCHAR(MAX), 0x313231, 0) AS ttttttttt;*/ /*SELECT SUM(CAST(CONVERT(VARCHAR(MAX), SUBSTRING(BinaryData, 0,2),0) AS DECIMAL(10, 4))) AS [text()] FROM @testTable t2 */ SELECT CONVERT(VARCHAR(MAX), SUBSTRING('0x313231', 2,2)) from @testTable; 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;