Pivot queries

Pivot queries involve transposing rows into columns (pivot) or columns into rows (unpivot) to generate results in crosstab format. Pivoting is a common technique, especially for reporting, and it has been possible to generate pivoted resultsets with SQL for many years and Oracle versions.

Lets explain this with an example:

1. The basic stucture of our table would be



2. We would like to see the output showing each of these 4 teams and their point tables for these 3 years.



3. Lets now see how to implement the requirement:

i) Here's the data

CREATE TABLE xx1 (Year1 NUMBER(4), team VARCHAR2(16), points NUMBER(3));
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India', 21);
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India1', 28);
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India2', 19);
INSERT INTO xx1 (Year1, team, points) VALUES (2009, 'India3', 10);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India', 18);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India1', 26);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India2', 2);
INSERT INTO xx1 (Year1, team, points) VALUES (2008, 'India3', 14);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India', 16);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India1', 27);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India2', 15);
INSERT INTO xx1 (Year1, team, points) VALUES (2007, 'India3', 10);

ii) Use "decode" to turn the year1 row into a column. Take a look at our "sparse" matrix.

SELECT team,
MAX (DECODE (year1, 2007, points, NULL)) Yr2007,
MAX (DECODE (year1, 2008, points, NULL)) Yr2008,
MAX (DECODE (year1, 2009, points, NULL)) Yr2009
FROM (SELECT year1, team, points FROM xx1)
GROUP BY team;