Aggregate sql resultset into HashBytes value

Question

Is there an equivalent of

CHECKSUM_AGG(CHECKSUM(*)) 

for HashBytes?

I know you can do

SELECT 
    HashBytes('MD5', 
        CONVERT(VARCHAR,Field1) + '|' 
      + CONVERT(VARCHAR,Field2) + '|'
      + CONVERT(VARCHAR,field3) + '|'
    )
FROM MyTable

But I am not sure how to aggregate all calculated hashbyte records into a single value inside of SQL.

One reason I would want to do this is to determine if data has changed in the source table since the previous load before moving the data into my system.

Answer 1

With newer versions of SQL Server, you can use a combination of CONCAT and STRING_AGG to bung everything together, then hash the whole result.

SELECT 
    HASHBYTES('SHA2_512',
      STRING_AGG(
        CONCAT(
          CAST(Field1 AS varchar(max)),  -- at least one max
          Field2,
          field3
        ), ''
      )
    )
FROM MyTable;

Note that MD5 is deprecated, and would probably be at risk of hash collisions even in this case. You should use SHA2_512 or SHA2_256 instead.