optimisteve optimisteve - 2 months ago 18
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:

dept


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

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


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

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

alter table dept
DROP COLUMN Row

DROP TABLE #TempFirstNametable

Answer
update  t1
set t1.title=b.title,
    t1.firstname=b.firstname
from dept t1
cross apply
(select  top 1  t2.title,t2.firstname
from 
dept t2
where
t1.title<>t2.title and t1.firstname<>t2.firstname
order by newid()
)b