How to Change Grading Terms in Pivot Reports

Several reports on this site use the Oracle Pivot command to create columns for Q1, Q2, Q3, and Q4 grading terms.  The following explains how the Pivot works and change the report to pull T1, T2, and T3 instead, or add additonal terms, such as S1 and S2. 

In addition to changing the sql, one will need to update the column headings area of the sqlReport to match the new information.

Here's a snippet of the sql for a report with the term information highlighted:

SELECT ..........Q1, Q2, Q3, Q4
FROM
(
select ..........
where .......... in ('Q1','Q2','Q3','Q4')
)
PIVOT
(..........in ('Q1' as Q1,'Q2' as Q2, 'Q3' as Q3, 'Q4' as Q4))

There are three parts with term information.  The second part, the one that deals with a where, narrows the results to pivot down to specific information.  It is optional, but is handy to narrow down the results so there's less for the Pivot to work through.  The third part, the one after the Pivot command, takes the results from the second part and assigns the information to aliases.  Those aliases are then referenced in the first part, the very first line in the example above.  If one is going to use different terms or add new terms, those three parts need to be changed or updated:

To have a report like above based on T1, T2, and T3 instead, the highlighted information would need to be changed to those terms:

SELECT .......... T1, T2, T3
FROM
(
select ..........
where .......... in ('T1','T2','T3')
)
PIVOT
(.......... in ('T1' as T1,'T2' as T2, 'T3' as T3))

To have a report like above, but with S1 and S2 added, one needs to add those terms:

SELECT .......... Q1, Q2, S1, Q3, Q4, S2
FROM
(
select ..........
where .......... in ('Q1','Q2','S1','Q3','Q4','S2')
)
PIVOT
(.......... in ('Q1' as Q1,'Q2' as Q2, 'S1' as S1, 'Q3' as Q3, 'Q4' as Q4, 'S2' as S2))


As mentioned above, once you make any changes, be sure to update the column headings are of the sqlReport as well.  If you remove terms or add new ones, you'll need to remove headings or add new ones too for the sqlReport to work correctly.

 

Back to the List of Articles