T-SQL SELECT Random varchar Value Without TempTable

user_574663 0
Viewed 357

Other then creating a temp table is there an elegant way to select a random column value in an inline query

SELECT [Col1],
       [Col2],
       ChooseRandomlyFrom('Lateral', 'AP', 'AP Ext Rot', 'PA', 'PA Obl', 'PA Pbl Int Rot', 'Lateral', 'L5 S1', 'PA Navicular'),
       [Col3]
FROM [dbo].[MyTable]

I would like random per row in a query to generate a sample data set

sql-server-2008 sql-server
2 Answers

You can achieve it by using a variable with CASE expression in following:

DECLARE @rand INT
SET @rand = ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % 3 + 1 

SELECT [Col1],
       [Col2],
       CASE @rand 
        WHEN 1 THEN 'A'
        WHEN 2 THEN 'B'
        WHEN 3 THEN 'C'
        ELSE 'D'
       END AS RandColValue, 
       [Col3]
FROM [dbo].[MyTable]

Or you could achieve it without variable in following:

SELECT [Col1],
       [Col2],
       CASE ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % 3 + 1 
        WHEN 1 THEN 'A'
        WHEN 2 THEN 'B'
        WHEN 3 THEN 'C'
        ELSE 'D'
       END AS RandColValue, 
       [Col3]
FROM [dbo].[MyTable]
user_46093600

You can achieve it by using a variable with Choose function

DECLARE @rand INT
SET @rand = ABS(CONVERT(BIGINT,CONVERT(BINARY(8), NEWID()))) % 3 + 1 

SELECT [Col1],
       [Col2],
       Choose(@rand,'A','B','C','D') AS RandColValue, 
       [Col3]
FROM [dbo].[MyTable]
user_83369490
Related