Pivot Rows into Columns (a Cross Tab)
Moderator: NorbertKrupa
Pivot Rows into Columns (a Cross Tab)
Is there a way that I can pivot the rows from a query into columns?
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Pivot Rows into Columns (a Cross Tab)
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:
If I select directly from the MLB table, my result set looks like this:
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:
We're not finished yet as we need to get rid of the NULL column values. For that, we use the MAX function:
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>>
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)
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)
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)
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.