Randomize the column data of a SQL table

Question

I have a table with two columns of text, where I want to randomize one column's data. So for example:

Text1    Text2
---------------
 ABC      DEF
 GHI      JKL
 MNO      PQR
 STU      VWX

to

Text1    Text2
---------------
 ABC      JKL
 GHI      VWX
 MNO      DEF
 STU      PQR

I am new to SQL and have no clue on how to do this.

Thanks in advance!

Put the title of your post into Google and start there. Give it a few tries and then let us know what issues you run into. — dfundako, Jun 23 '16 at 17:19
Possible duplicate of [How to randomly select rows in SQL?](http://stackoverflow.com/questions/580639/how-to-randomly-select-rows-in-sql) — Edu, Jun 23 '16 at 17:22

score 0 · Answer 1 · answered Jun 23 '16 at 17:33

try this:

SAMPLE DATA:

IF OBJECT_ID('tempdb..#Text') IS NOT NULL
    DROP TABLE #Text;

CREATE TABLE #Text(Text1 VARCHAR(10)
              , Text2 VARCHAR(10));

INSERT INTO #Text
VALUES
      ('ABC'
     , 'DEF'),
      ('GHI'
     , 'JKL'),
      ('MNO'
     , 'PQR'),
      ('STU'
     , 'VWX');

QUERY:

SELECT Text1
    , Text2
FROM
      (SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) AS SNO
           , Text1
       FROM   #Text) AS A
      INNER JOIN
              (SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) AS SNO
                   , Text2
               FROM   #Text) AS B ON A.SNO = B.SNO;

RESULT:

Randomize the column data of a SQL table

1 Answers1