CODEWITHSUNDEEP
sql-servert-sql
Lynchie
Lynchie

Reputation: 1149

Choose 2 Random Values from 2 Separate Columns SQL

I am having to prepare test data to send to a 3rd party however I don't wish to send the customers real name nor do I wish to send their real date of birth.

I could solve the D.O.B issue by just randomly increasing the DOB by several years. However the name is different, is there anyway I can have a list of say 10 customer names and just choose a different Firstname and Surname each time.

I wish to mix and match the names however so it essentially randomly picks 1 firstname and then randomly picks a lastname and puts them together on the same line.

SELECT TOP 1 opde.first_name 
FROM Table AS opde
ORDER BY NEWID()

This will return a random first name each time, but if I put the surname column in it will also return the matching surname, I don't want that I want a random surname from the list.

I tried doing this via a UNION but you can't do an ORDER BY NEWID() in the UNION.

Cheers.

Upvotes: 0

Views: 217

Answers (2)

jigga
jigga

Reputation: 614

I think this one might help... 

WITH fn AS
(
    SELECT TOP 1 opde.first_name
    FROM Table AS opde 
    ORDER BY NEWID()
),
sn AS
(
    SELECT TOP 1 opde.surname
    FROM Table AS opde 
    ORDER BY NEWID()
)
SELECT first_name, surname
FROM fn
CROSS APPLY sn;

In the fn subquery you select a random first name. In the sn you do the same but with an surname. With the cross apply you combine those two results

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

You may use a union of subqueries, each of which uses order by with NEWID:

SELECT first_name
FROM
(SELECT TOP 1 opde.first_name FROM Table AS opde ORDER BY NEWID()) t1
UNION ALL
SELECT first_name
FROM
(SELECT TOP 1 opde.first_name FROM Table AS opde ORDER BY NEWID()) t2;

Demo

Upvotes: 0

Related Questions