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)
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)
Have fun!