PIVOT Query Example

[JimKnicely]

A pivot or cross-tab query is a transformation of rows of data into columns. SQL Server and Oracle have built in functionality to help facilitate these types of queries. Unfortunately Vertica does not, but that doesn’t mean we can’t perform pivots in Vertica!

Here is a quick example.

Pretend we have a table named USER_RESPONSE that tracks user responses to three separate perplexing questions:

dbadmin=> SELECT * FROM user_response ORDER BY name, question_no;

Code: Select all

name | question_no | response
------+-------------+----------
Jim  |           1 | Yes
Jim  |           2 | Yes
Jim  |           3 | No
Peng |           1 | Yes
Peng |           2 | Unknown
Peng |           3 | No
Tom  |           1 | Maybe
Tom  |           2 | No
Tom  |           3 | No
(9 rows)

We want to pivot this data on the distinct row values in the response column and get a count of each response type by user.

In SQL Server 2008 R2 we can use the PIVOT relational operator:



In Vertica we have to be a little more creative and exploit a combination of the DECODE and SUM functions:

Code: Select all

dbadmin=> SELECT name,
dbadmin->        SUM(DECODE(response, 'Yes', 1, 0))     "yes",
dbadmin->        SUM(DECODE(response, 'No', 1, 0))      "no",
dbadmin->        SUM(DECODE(response, 'Maybe', 1, 0))   "maybe",
dbadmin->        SUM(DECODE(response, 'Unknown', 1, 0)) "unknown"
dbadmin->   FROM user_response
dbadmin->  GROUP
dbadmin->     BY name;
name | yes | no | maybe | unknown
------+-----+----+-------+---------
Jim  |   2 |  1 |     0 |       0
Peng |   1 |  1 |     0 |       1
Tom  |   0 |  2 |     1 |       0
(3 rows)

Hmm. I wonder what the three questions were?

Have fun!