Rotating Column Data Using Analytics
Posted: Mon Apr 08, 2013 4:05 pm
For this tip we’ll be using a table named X having the following data:
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:
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:
And then ran the modified UPDATE statement:
We’ve successfully “rotated” the data in column C2
Have fun!
Code: Select all
dbadmin=> SELECT * FROM x ORDER BY 1;
c1 | c2
----+---------
1 | Jim
2 | Peng
3 | Tom
4 | Mark
5 | Patrick
(5 rows)
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)
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)
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)
Have fun!