pivot
Ever saw oracle’s official documentation on the SQL PIVOT ? Couldn’t find the matching documentation for it, and no examples? I have turned the PIVOT inside-out, and will share you what it does.
Starting with pages of unrelated data points
SELECT student, subject, minor, quarter, score, score_max FROM metric
student | subject | minor | quarter | score | score_max |
Jeff | Math | Algebra | 1 | 14 | 20 |
Jeff | Math | Trigonometry | 1 | 14 | 20 |
Jeff | English | Grammar | 2 | 9 | 10 |
Jeff | English | Literature | 2 | 9 | 10 |
… |
This table basically stores for each student his scores on a couple of subjects, further subdivided into minor subject, and for each individual quarter of the year.
Now, lets try to apply a full fledged PIVOT, including all whistles and bells …
SELECT * FROM ( SELECT student, subject, minor, score, score_max FROM metric ) PIVOT ( COUNT(DISTINCT quarter) AS "CNT", SUM(score) AS num, SUM(score_max) AS den FOR (subject,minor) IN ( ('Math','Trigonometry') AS "M_TRI", ('Math','Algebra') AS "M_ALG", ('English','Grammar') AS "E_GRA", ('English','Literature') AS "E_LIT" ) )
And again the output … maybe to some surprises …
student | m_tri_cnt | m_tri_num | m_tri_den | m_alg_cnt | m_alg_num | m_alg_den | e_gra_cnt | e_gra_num | e_gra_den | e_lit_cnt | e_lit_num | e_lit_den |
Jeff | 2 | 28 | 40 | 2 | 14 | 20 | 4 | 69 | 100 | 3 | 356 | 400 |
Mieke | 3 | 38 | 40 | 2 | 18 | 20 | 4 | 69 | 100 | 4 | 301 | 400 |
… |
Ok, we all expected rows be turned into columns based on some determinator. But a lot more happened, that is obvious. I will list out –
FOR (determinators)
identifies the determinators used to identify the pivoted columns, in this case a combination of two columns(subject,minor)
.IN (...)
lists out any combination of the determinators, and the associated alias (the new column name).- The first part within the
PIVOT (aggregators FOR ...)
, the aggregators (MIN
,MAX
,COUNT
, …). These aggregators basically describe a method to be used to collapse multiple rows into one. In the simplified case, the chosen aggregation method wont matter, But in this example it does. Any column not used in the aggregator sector, will be part of an implicitGROUP BY
. When providing multiple aggregators, you need to associate each aggregator with it’s own alias. This alias will be concatenated at the end of the alias provided for the determinator alias.
English is a very ambiguous medium to try to explain something like this, but this is why the provided examples should shed light on the obscurities. Don’t forget to keep the Oracle SQL Reference Guide handy.
We started with the most complex example. Now to show that it does not need to be always that verbose, we will simplify this example. We will assume:
- All scores are 100-based, and to come to a total score we can just
SUM(...)
- There are no minor subjects anymore
- No quarters either, just a list of students and their score for each main subject
SELECT student, subject, score FROM metric
student | subject | score |
Jeff | Math | 80 |
Jeff | English | 93 |
… |
Now, if we want to pivot this, it becomes much more simpler, and less verbose:
SELECT * FROM metric PIVOT ( SUM(score) FOR (subject) IN ('Math','English') )
What simplified this is the following:
- With a single aggregator, no more need for a separate alias suffix.
- Single determinator.
- The list of determinator combinations does not have an alias defined either. However Columns will appear literally as the expression, showing ‘Math’, including the single quotes.
- The pivoted query is now just the table itself, no subquery.
student | ‘Math’ | ‘English’ |
Jeff | 212 | 534 |
Joe | 365 | 653 |
John | 456 | 543 |
Jo-Ann | 383 | 645 |
… |
December 20, 2013 at 12:40 pm
Hi,
I have a question, hope you will find time to explore a solution.
I need to pivot dynamic data into a view that the number of the columns is not fix.
For example, I have a table name Catalog Group with fields/columns:
CATALOG_GROUP_NAME, ELEMENT_NAME, ELEMENT_VALUE.
one Catalog Group can have 5 different elements and other one could be with 90 different. (because it is a flexible table, in ERP Oracle).
There is also a join with item table that for each item I can assign different Catalog Group (the join is with catalog_group_id).
So, how I can create a Pivot View that will create different output for different item. for item A I have a catalog group with 5 values and for item B I have catalog name with 90 values.
ITEM CATALOG_GROUP_NAME ELEMENT_NAME ELEMENT_VALUE
RTAC3-CHA*2581617 RTAC3-CHA AGLT CE
RTAC3-CHA*2581617 RTAC3-CHA AOPT PNT
RTAC3-CHA*2581617 RTAC3-CHA CACC UOVM
RTAC3-CHA*2581617 RTAC3-CHA CDFN STDC
RTAC3-CHA*2581617 RTAC3-CHA CDMT STDX
RTAC3-CHA*2581617 RTAC3-CHA CDTY HA
other item will have only 2 recordes/column
ITEM CATALOG_GROUP_DESC ELEMENT_NAME ELEMENT_VALUE
FIT-CGAMS-000*2521613 NO_DATA_FOUND Unit Type CGAM
FIT-CGAMS-000*2521613 NO_DATA_FOUND Unit Size 60
Appreciate your time and efforts that you are going to spend on it,
Thanks a lot,
David
January 5, 2014 at 5:49 am
If your result set is as dynamic as you say it is (Each Catalog Group will have an unpredetermined set of elements ranging 5 through 90), then I do not think a plain SQL Pivot solution is ideal. For your case, I would propose to go with a programmatic solution in PL/SQL, Java, or other programming language that allows you to better process result sets.