cancel
Showing results for 
Search instead for 
Did you mean: 

SQL statement to shuffle a column in a table

huber1
Participant
4,551

I am looking for an SQL statement to shuffle the last name column in a persons table. To be able to make a demo on the basis of existing data. A solution with the ability to shuffle more than one column in one statement would be welcome.

Thanks a lot and best regards, Robert

VolkerBarth
Contributor

Please show us the sample table schema, some initial data and the desired "shuffled" outcome.

huber1
Participant
0 Kudos

Thanks, Volker, for asking.

The (simplified) content of the table is:

last name       first name      sex code
Meier           Eva             w
Frutiger        Urs             m
Barth           Maya            w

...and after shuffling (the last name), the outcome would be for example like this:

last name       first name      sex code
Barth           Eva         w
Meier           Urs         m
Frutiger        Maya        w

It would be nice to have a SQL statement where I can shuffle on a condition like sex code, i. e. shuffling only first names which have sex code w, for example.

Hope this clarifies it a bit.

Breck_Carter
Participant
0 Kudos

What does "shuffle" mean? Exactly like a card deck, where the values are preserved but the order is randomized? What are the constraints?

VolkerBarth
Contributor

FWIW, are you trying to randomize real data to generate test data or to "anonymize" data?

huber1
Participant
0 Kudos

@Volker: There are not much constraints, as long as the name is not obviously recognisable it's ok. It's not that I need a strict randomisation of the data.

@Breck: It would not even be necessary to preserve the last names. Preserving the names and putting them in a different order would be ok. As far as I understand you, if the last names wouldn't preserved, they would have to be newly generated? That is not needed.

Vlad
Product and Topic Expert
Product and Topic Expert
0 Kudos

I don't want to stop the party here, but why don' you export the data as CSV, use Excel to "shuffle" the data and update the existing records in the new (or the same) database?

You need the result fast, or you want to invest the time into the SQL statement? 🙂

Accepted Solutions (1)

Accepted Solutions (1)

VolkerBarth
Contributor

You can use a WINDOW clause to access the GivenName (or whatever value) of a neighbouring row.

As you have not supplied a test script, here's a sample from the SQL Anywhere (V16) Demo database, there's a table "employees" with the mentioned columns Surname, GivenName and Sex.

In that case, I have reduced the access to employess of one department and have rotated their surnames, so that each person gets the Surname of the previous person (ordered by EmployeeID) but separated for the sexes.

select EmployeeID, Surname,
   isnull(
      first_value(Surname) over
         (partition by Sex order by EmployeeID
          rows between 1 preceding and 1 preceding),
      last_value (Surname) over
         (partition by Sex order by EmployeeID
          rows between 1 following and unbounded following))
   as PreviousRotatedSurname,
   GivenName, Sex
from Employees
where DepartmentID = 400
order by Sex, EmployeeID 

Note the usage of the ISNULL() expression to supply the first person of each partition (i.e. the first female/male) with the surname of the last person.

The result contains both the original surname and the rotated value.

Result:

(Ah, I want to upload an image, however, that fails again...)

alt text

So here's the result as text output:

 EmployeeID Surname              PreviousRotatedSurname GivenName            Sex 
----------- -------------------- ---------------------- -------------------- --- 
        184 Espinoza             Hildebrand             Melissa              F   
        207 Francis              Espinoza               Jane                 F   
        591 Barletta             Francis                Irene                F   
        992 Butterfield          Barletta               Joyce                F   
       1062 Blaikie              Butterfield            Barbara              F   
       1507 Wetherby             Blaikie                Ruth                 F   
       1643 Lambert              Wetherby               Elizabeth            F   
       1684 Hildebrand           Lambert                Janet                F

        318 Crow                 Ahmed                  John                 M   
        409 Weaver               Crow                   Bruce                M   
        888 Charlton             Weaver                 Doug                 M   
       1191 Bucceri              Charlton               Matthew              M   
       1576 Evans                Bucceri                Scott                M   
       1607 Morris               Evans                  Mark                 M   
       1740 Nielsen              Morris                 Robert               M   
       1751 Ahmed                Nielsen                Alex                 M   
  

I don't know whether you might finally need to make sure that the original surname and the "rotated" one are different.


Aside: A completely different approach would be to use a CROSS JOIN between persons of the same sex and join when their surnames are different. Of course that will usually generate many more rows than the original table has. Just saying.

huber1
Participant
0 Kudos

What I was looking for 🙂 Thanks very much, Volker

VolkerBarth
Contributor
0 Kudos

If that solved your question, feel free to mark the answer as accepted.

huber1
Participant
0 Kudos

Of course I will do that Volker, thought it was easy to convert this to an UPDATE statement, but doesn't seem so. As in the end I'd like to update the surname column, I tried:

UPDATE Employees
SET Surname = 
   isnull(
      first_value(Surname) over
         (partition by Sex order by EmployeeID
          rows between 1 preceding and 1 preceding),
      last_value (Surname) over
         (partition by Sex order by EmployeeID
          rows between 1 following and unbounded following))
where DepartmentID = 400

But that doesn't work. Any idea how to convert the SELECT statement into an UPDATE of the Surname?

VolkerBarth
Contributor

I'd put the original SELECT into a derived query (say, named dt) and then join the original TABLE with that dt, such as

UPDATE Employees e
SET SurName = dt.PreviousRotatedSurname
FROM Employees e
   INNER JOIN (...< the original SELECT, probably without the ORDER BY> ...) dt
      ON e.EmployeeID = dt.EmployeeID
ORDER BY e.EmployeeID;

Note, for such ad-hoc updates, I do always use a SELECT to check whether the join will work as expected...

Answers (0)