Rotating Column Data Using Analytics

[JimKnicely]

For this tip we’ll be using a table named X having the following data:

Code: Select all

dbadmin=> SELECT * FROM x ORDER BY 1;
 c1 |   c2
----+---------
  1 | Jim
  2 | Peng
  3 | Tom
  4 | Mark
  5 | Patrick
(5 rows)

Now we want to rotate the values in column C2 up by one row. That is, we want the new value of C2 to become the next value of C2 so that “Jim” becomes “Peng”, “Peng” becomes “Tom”, “Tom” becomes “Mark”, etc.

To accomplish this we can use the analytic function LEAD to grab the next value of C2 for every row and use it in an UPDATE statement:

Code: Select all

dbadmin=> UPDATE x
dbadmin->    SET c2 = (SELECT lead_c2
dbadmin(>                FROM (SELECT c1, LEAD (c2, 1) OVER (ORDER BY c1) lead_c2
dbadmin(>                        FROM x) foo
dbadmin(>               WHERE foo.c1 = x.c1);
 OUTPUT
--------
      5
(1 row)

dbadmin=> SELECT * FROM x ORDER BY 1;
 c1 |   c2
----+---------
  1 | Peng
  2 | Tom
  3 | Mark
  4 | Patrick
  5 |
(5 rows)

That almost worked, except we were left with a NULL value in C2 for row five. We really want row five’s data for column c2 to be “Jim”.

We need to slightly modify our UPDATE statement to include another analytic function called FIRST_VALUE. We’ll use that value when the LEAD function returns a NULL.

First, I restored the original data in table X:

Code: Select all

dbadmin=> SELECT * FROM x ORDER BY 1;
 c1 |   c2
----+---------
  1 | Jim
  2 | Peng
  3 | Tom
  4 | Mark
  5 | Patrick
(5 rows)

And then ran the modified UPDATE statement:

Code: Select all

dbadmin=> UPDATE x
dbadmin->    SET c2 = (SELECT new_c2
dbadmin(>                FROM (SELECT c1,
dbadmin(>                             NVL(LEAD (c2, 1) OVER (ORDER BY c1),
dbadmin(>                               FIRST_VALUE(c2) OVER (ORDER BY c1)) new_c2
dbadmin(>                        FROM x) foo
dbadmin(>               WHERE foo.c1 = x.c1);
 OUTPUT
--------
      5
(1 row)

dbadmin=> SELECT * FROM x ORDER BY c1;
 c1 |   c2
----+---------
  1 | Peng
  2 | Tom
  3 | Mark
  4 | Patrick
  5 | Jim
(5 rows)

We’ve successfully “rotated” the data in column C2

Have fun!