How to select columns from table in random order in TSQL

Question

I have a table with 5 columns in it, what's the easiest way to select all rows, but where each column is individually randomised?

All I can think of is to select each column separately, along with

row_number over ( order by newid()) as lookup

and then join each column back together on lookup.

Is there an easier way?

Thanks

Answer 1

No, this is pretty much it. I assume you want random row order per column (not all columns from the same random row).

SQL is row based, so for 5 columns you need to separate each column selection (for the random row per column) and then use the arbritrary row to join the results togethers.

An interesting problem...

Answer 2

You can prepare 5 ordered sets and PIVOT them:

SELECT  *
FROM    (
        SELECT  'col1' AS name, col1 AS col, ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
        FROM    table
        UNION ALL
        SELECT  'col2', col2, ROW_NUMBER() OVER (ORDER BY NEWID())
        FROM    table
        UNION ALL
        SELECT  'col3', col3, ROW_NUMBER() OVER (ORDER BY NEWID())
        FROM    table
        UNION ALL
        SELECT  'col4', col4, ROW_NUMBER() OVER (ORDER BY NEWID())
        FROM    table
        UNION ALL
        SELECT  'col5', col5, ROW_NUMBER() OVER (ORDER BY NEWID())
        FROM    table
        ) q
PIVOT   (
        MIN(col)
        FOR name IN ([col1], [col2], [col3], [col4], [col5])
        ) pt

See this entry in my blog for more detail:

• Ordering columns independently