Pivot Rows into Columns (a Cross Tab)

[Jbaskin]

Is there a way that I can pivot the rows from a query into columns?

[JimKnicely]

You can "pivot" rows into columns via the DECODE function.

Here is a quick example using runs scored per season by a few MLB teams (Note: The scores are made up because I was too lazy to Google actuals).

First I'll create a table name MLB and insert some data:

Code: Select all

dbadmin=> create table mlb (season INT, team varchar(100), runs int);
CREATE TABLE
dbadmin=>
dbadmin=> insert into mlb values (2008, 'Pirates', 350);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into mlb values (2009, 'Pirates', 325);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into mlb values (2010, 'Pirates', 296);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into mlb values (2011, 'Pirates', 301);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into mlb values (2008, 'Yankees', 450);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into mlb values (2009, 'Yankees', 487);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into mlb values (2010, 'Yankees', 387);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into mlb values (2011, 'Yankees', 404);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into mlb values (2008, 'Dodgers', 250);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into mlb values (2009, 'Dodgers', 347);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into mlb values (2010, 'Dodgers', 237);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into mlb values (2011, 'Dodgers', 544);
 OUTPUT
--------
      1
(1 row)

If I select directly from the MLB table, my result set looks like this:

Code: Select all

dbadmin=> SELECT * FROM mlb;
 season |  team   | runs
--------+---------+------
   2008 | Pirates |  350
   2009 | Pirates |  325
   2010 | Pirates |  296
   2011 | Pirates |  301
   2008 | Yankees |  450
   2009 | Yankees |  487
   2010 | Yankees |  387
   2011 | Yankees |  404
   2008 | Dodgers |  250
   2009 | Dodgers |  347
   2010 | Dodgers |  237
   2011 | Dodgers |  544
(12 rows)

I'd like to pivot my results on the SEASON column so that I can see the runs scored in columns as opposed to in the rows. To do that I can use the DECODE function:

Code: Select all

dbadmin=> SELECT team
dbadmin->      , DECODE(season, 2008, runs, NULL) AS "2008_Season"
dbadmin->      , DECODE(season, 2009, runs, NULL) AS "2009_Season"
dbadmin->      , DECODE(season, 2010, runs, NULL) AS "2010_Season"
dbadmin->      , DECODE(season, 2011, runs, NULL) AS "2011_Season"
dbadmin->   FROM (SELECT season
dbadmin(>              , team
dbadmin(>              , runs
dbadmin(>           FROM mlb) seasons;
  team   | 2008_Season | 2009_Season | 2010_Season | 2011_Season
---------+-------------+-------------+-------------+-------------
 Pirates |         350 |             |             |
 Pirates |             |         325 |             |
 Pirates |             |             |         296 |
 Pirates |             |             |             |         301
 Yankees |         450 |             |             |
 Yankees |             |         487 |             |
 Yankees |             |             |         387 |
 Yankees |             |             |             |         404
 Dodgers |         250 |             |             |
 Dodgers |             |         347 |             |
 Dodgers |             |             |         237 |
 Dodgers |             |             |             |         544
(12 rows)

We're not finished yet as we need to get rid of the NULL column values. For that, we use the MAX function:

Code: Select all

dbadmin=> SELECT team
dbadmin->      , MAX(DECODE(season, 2008, runs, NULL)) AS "2008_Season"
dbadmin->      , MAX(DECODE(season, 2009, runs, NULL)) AS "2009_Season"
dbadmin->      , MAX(DECODE(season, 2010, runs, NULL)) AS "2010_Season"
dbadmin->      , MAX(DECODE(season, 2011, runs, NULL)) AS "2011_Season"
dbadmin->   FROM (SELECT season
dbadmin(>              , team
dbadmin(>              , runs
dbadmin(>           FROM mlb) seasons
dbadmin->  GROUP
dbadmin->     BY team;
  team   | 2008_Season | 2009_Season | 2010_Season | 2011_Season
---------+-------------+-------------+-------------+-------------
 Dodgers |         250 |         347 |         237 |         544
 Yankees |         450 |         487 |         387 |         404
 Pirates |         350 |         325 |         296 |         301
(3 rows)

That's it! This is an old trick we used in Oracle before they introduced the PIVOT SQL command in 11g. Maybe Vertica will introduce a similar feature! <<HINT>>