Partition distinct masking values by unique values in another (non-PK) column

I'd like to know if anyone has a solution for the following setup:

dbo.CaseHistoryAudit

Id  UserGUID  Username           Action
1   123A......     Alice Bloggs       EditedField_1
2   852C......     Bob Ward           DeletedNotes
3   741D......     John Smith         SavedForm
4   123A......     Alice Bloggs        PrintedDoc
5   987B......     Charlie Dawson  ClosedClaim
6   123A......     Alice Bloggs        SavedForm
7   852C......     Bob Ward            PrintedDoc

The requirement is to mask the username, which is easily done using a substitution rule.

I cannot formulate a simple (yet performant) method to mask the "Username" column such that each Unique UserGUID results with the same name.  In the example, I'd like rows 1,4 and 6 to retain the same masked value, so Alice would be changed to something Random from the dataset like "Peter" but "Peter" would appear on rows 1,4 and 6.

This feels like functionality which ought to have been baked into the product but I'm not seeing an easy solution jumping out at me.  Can anyone help please?

Answers

Sign In or Register to comment.