Rotating Column Data Using SQL Analytics: Quick Tip

Jim Knicely authored this post.

Vertica analytics are SQL functions based on the ANSI 99 standard. These functions handle complex analysis, reporting tasks, and allow for some cool data manipulation.

Example:

I have the following table of names where each name has a unique ID. I want to rotate the values in column NAME up by one row. That is, I want the new value of NAME to become the next value of NAME so that “Dave” becomes “Kristen”, “Kristen” becomes “Andrius”, “Andrius becomes “Curtis”, etc. dbadmin=> SELECT * FROM test ORDER BY id; id | name ----+--------- 1 | Dave 2 | Kristen 3 | Andrius 4 | Curtis 5 | Lenoy 6 | Scott (6 rows) I can easily accomplish this task using the Vertica analytic SQL functions LEAD and FIRST_VALUE! dbadmin=> UPDATE test dbadmin-> SET name = (SELECT new_name dbadmin(> FROM (SELECT id, NVL(LEAD (name, 1) OVER (ORDER BY id), FIRST_VALUE(name) OVER (ORDER BY id)) new_name dbadmin(> FROM test) foo dbadmin(> WHERE foo.id = test.id); OUTPUT -------- 6 (1 row) dbadmin=> SELECT * FROM test ORDER BY id; id | name ----+--------- 1 | Kristen 2 | Andrius 3 | Curtis 4 | Lenoy 5 | Scott 6 | Dave (6 rows) Helpful links:

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/AnalyzingData/SQLAnalytics/UsingSQLAnalytics.htm

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Analytic/LEADAnalytic.htm

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Functions/Analytic/FIRST_VALUEAnalytic.htm

Have fun!