title: How to combine binary chunks in SQL across rows?
body: <p>We have a requirement to concatenate chunks of binary data in a SQL Server 2014 database. Our requirement for this is due to slow replication of varbinary(max) data over a WAN link, as described here - 
  <a href="https://stackoverflow.com/questions/28965983/slow-merge-replication-over-a-wan-link-only-downloads">Slow merge replication over a WAN link - only downloads</a> so we're having to reduce our varbinary(max) columns to smaller widths.</p>
  
  <p>Given a table and some dummy data: </p>
  
  <pre><code>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)
  </code></pre>
  
  <p>I can write T-SQL to concatenate data for the rows corresponding to a single ItemIdx:</p>
  
  <pre><code>DECLARE @concatData varbinary(max)
  SELECT @concatData = COALESCE(@concatData, 0x) + BinaryData FROM @testTable
  WHERE ItemIdx = 1
  ORDER BY ChunkIdx
  </code></pre>
  
  <p>which results in <code>0x001122338899AABB44556677</code>, the intended result for a single ItemIdx.<br><br>
  Is there any way to concatenate for all ItemIdx entries in a neat way?
  My desired result is something like:<br></p>
  
  <pre><code>ItemIdx | Data
  1       | 0x001122338899AABB44556677
  2       | 0xFFEEDDCC77665544BBAA9988
  </code></pre>
  
  <p>CLR would be a very sensible alternative, although the lack of support for table valued parameters makes any approach I can think of awkward. I've had various attempts using GROUP BY, COALESCE, FOR XML, CTEs and a couple of of others but no luck.</p>