optimisteve optimisteve - 1 year ago 86
SQL Question

Scrambling records within a column

I am currently preparing some Test data and require anonymizing of the original records. So I would like to scramble records within a column without affecting other columns in thesame Row.

Mr Adam in Row 1 will be Mr Adam in Row 4....other details about Mr Adam are to be retained in Row 1.

Below is an example

Table name:


Title Firstname Lastname Telephone other columns...
1 Mr Adam Smith 001
2 Mrs Angela Evans 002
3 Mr Bill Towny 003
4 Miss Dame Beaut 004

I am interested in transforming it as per below

Title Firstname Lastname Telephone other columns...
1 Miss Dame Smith 001
2 Mr Bill Evans 002
3 Mrs Angela Towny 003
4 Mr Adam Beaut 004

You will notice ONLY the firstname and associated title has been scrambled...other fields such as lastname and telephone remain the same...

Below is my attempt...I couldn't get the Title to be scrambled along with the firstname

FirstName as TempFirstNamecolumn, ROW_NUMBER() OVER (ORDER BY NEWID()) As Rowno
Into #TempFirstNametable
FROM dept

alter table dept
add Row int identity(1,1)

SET FirstName = #TempFirstNametable.TempFirstNamecolumn
FROM #VirtualFirstNametable WHERE Row= Rowno

alter table dept

DROP TABLE #TempFirstNametable

Answer Source
update  t1
set t1.title=b.title,
from dept t1
cross apply
(select  top 1  t2.title,t2.firstname
dept t2
t1.title<>t2.title and t1.firstname<>t2.firstname
order by newid()
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download