cancel
Showing results for 
Search instead for 
Did you mean: 

SQL statement to shuffle a column in a table

huber1
Participant
4,674

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

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)