How to hashbytes SQL information for one record spanning multiple rows 1

[bmacbmac]

Hello. Running SQL 2008.

I have the following sample data:

create table #temp (irecordid int, name varchar(100))
insert into #temp values (2373691, 'HURT, CYNTHIA')
insert into #temp values (2373691 , 'HURT, STEVEN')
insert into #temp values (2378322 , 'SUNSET CUSTOM HOMES, ')
insert into #temp values (2378322 , 'TORRES, NOE')
insert into #temp values (2378322 , 'NOE TORRES INC, ')
insert into #temp values (2373827, 'MILLER, BEVERLY')
insert into #temp values (2373827, 'MILLER, RONNIE')


I'm hoping to get a single result of the irecordid and a hash of all of the 'name' entries for each irecordid. For example:
Irecordid HASH
2373691 0x42DDD2300DAB2A93AC7DDF64FF479ECD
2378322 0x0D517BF2056B62FFA116A27B53D3B31D
2373827 0xDB2D7575E3A72E028DF0F8DF8EFF8933


I can get the results one at a time with the following query:

select distinct irecordid, HASHBYTES('md5', CONVERT(varbinary(max), (select name from #temp where irecordid = 2373827 for xml auto )))
from #temp
where irecordid = 2373827

However, I'd like to get this with one script for my entire table. I have tried with grouping, but just can't get my syntax correct.

Any suggestions?

Thanks!

Brian

 

[Olaf Doschke]

Why that complicated?

Untested, but this should work as simple as

SELECT irecordid, HASHBYTES('md5', name) as HASH FROM #temp

https://msdn.microsoft.com/en-us/library/ms174415(v=sql.105).aspx

[tt]HashBytes ( '<algorithm>', { @input | 'input' } )[/tt]

This syntax means you can use a variable or string. Where a string is allowed, you can also allow a single char/varchar/nchar/nvarchar column. No need for a FOR XML query.

And if you would need multiple columns, there is a recommended solution in the community additions to the help topic:

SELECT name, 
  [HASHKEY] = (  HASHBYTES('MD5', ( select name, database_id, create_date , source_database_id FOR XML RAW)))  
FROM SYS.DATABASES

This would translate to this in your case:

SELECT irecordid, 
  [HASH] = (  HASHBYTES('MD5', ( select name FOR XML RAW)))  
FROM #temp

Since your hash only is from one field, this is unnecessary. Also, multiple string fields could simply be concatenated to one value, eg [tt]HASHBYTES('MD5',Firstname+Lastname)[/tt] would work, when a table has firstname and lastname columns.

Bye, Olaf.

 

[bmacbmac]

Thanks for the reply Olaf.

Hashing a single row is simple. Especially using your example:

SELECT irecordid, HASHBYTES('md5', name) as HASH FROM #temp

Which gives me the results:
2373691 0x6AC684AC73C1E97E8687A4C36EBDF278
2373691 0xE11DDD7308A696277FBFD6B43CC426DD
2378322 0x3427FDEBE8CC340AC607FC4DC05C5180
2378322 0xFF6F955006259F8C7D404C6290247B35
2378322 0x972BBEE62828DD55B15F888899039682
2373827 0x14E9E578BA41A6C4A60BCDB22105856A
2373827 0x31A98A46C37C90485F4382D135B5A44C


The problem I'm having is that I'd like to hash all rows for EACH irecordid grouped together. Instead of receiving results like those above (one hash for each row). I'd like results like results shown below (one hash for each irecordid):

2373691 0x42DDD2300DAB2A93AC7DDF64FF479ECD
2378322 0x0D517BF2056B62FFA116A27B53D3B31D
2373827 0xDB2D7575E3A72E028DF0F8DF8EFF8933

Any idea how I can group this?

Thanks!
Brian

 

[DaveInIowa]

How about...

SELECT irecordid,
       (SELECT '' + name FROM #temp WHERE irecordid = t.irecordid ORDER BY name ASC FOR XML PATH('')) ConcatNames,
       HASHBYTES('md5', (SELECT '' + name FROM #temp WHERE irecordid = t.irecordid ORDER BY name ASC FOR XML PATH(''))) HashNames
  FROM #temp t
 GROUP BY irecordid

 

[bmacbmac]

Yes!! Thanks DaveInIowa. That's exactly what I needed.

 

[Olaf Doschke]

Sorry, for not looking at the data in detail. The first impression I had is #temp is already grouped by irecordid. Why else a temp table? If you want to prepare some sample data for testing in a posting, I'd prefer and suggest using a table variable.

Bye, Olaf.