The best strategy to pivot a desk is to make use of the next perform: decode()The DECODE() perform is like an if else assertion: it compares the enter with every worth and produces an output.
DECODE(enter, value1, return1, value2, return2, …, default)
- Enter worth: “Enter” is in comparison with all “Values”.
- return: If enter = worth, then “return” is the output.
- Default (non-compulsory): If the enter just isn’t equal to all values, “default” is output.
Now that you understand how DECODE() works, it is time to create your first pivot desk.
First model: Pivot desk with out whole columns and rows
DECODE() can be utilized to map out the pseudocode for a pivot desk for an ice cream store proprietor. If “day of week” matches every day of the week, DECODE() will return the income for that day. If there is no such thing as a match, it would return 0 as a substitute.
SELECT ice cream taste,
SUM(DECODE(day of the week, 'Monday', income, 0)) AS MONDAY, SUM(DECODE(day of the week, 'Tuesday', income, 0)) AS TUESDAY,
SUM(DECODE(day of the week, 'Wednesday', income, 0)) AS WEDNESDAY,
SUM(DECODE(day of the week, 'Thursday', income, 0)) AS THURSDAY,
SUM(DECODE(day of the week, 'Friday', income, 0)) AS FRIDAY,
SUM(DECODE(day of the week, 'Saturday', income, 0)) AS SATURDAY,
SUM(DECODE(day of the week, 'Sunday', income, 0)) AS SUNDAY
FROM ice cream store dataset
WHERE date between final Monday and final Sunday;
Second model: Pivot desk with whole columns and rows
Nice job! Now the ice cream store proprietor needs to know extra about final week’s gross sales. You possibly can improve your pivot desk by including whole columns and rows.
that is, GROUPING SETS Expressions In a GROUP BY assertion, the GROUPING SETS expression defines the circumstances for a number of GROUP BY aggregations.
grouping set (attribute1, …, ())
- attribute: A single ingredient or checklist of parts to GROUP BY
- (): An empty group that serves as the whole row of the pivot desk
SELECT NVL(ice cream taste, 'TOTAL') "ICE CREAM FLAVOR",
SUM(DECODE(day of the week, 'Monday', income, 0)) AS MONDAY, SUM(DECODE(day of the week, 'Tuesday', income, 0)) AS TUESDAY,
SUM(DECODE(day of the week, 'Wednesday', income, 0)) AS WEDNESDAY,
SUM(DECODE(day of the week, 'Thursday', income, 0)) AS THURSDAY,
SUM(DECODE(day of the week, 'Friday', income, 0)) AS FRIDAY,
SUM(DECODE(day of the week, 'Saturday', income, 0)) AS SATURDAY,
SUM(DECODE(day of the week, 'Sunday', income, 0)) AS SUNDAY,
SUM(income) AS TOTAL
FROM ice cream store dataset
WHERE date between final Monday and final Sunday
GROUP BY GROUPING SETS (ice cream taste, ());
Word: NVL() replaces the null rows created by () with “TOTAL”. NVL()which is solely a perform that replaces null values.
One other strategy to calculate the TOTAL column is so as to add up all of the income from Monday to Sunday.
SUM(DECODE(day of the week, 'Monday', income, 0))
+ SUM(DECODE(day of the week, 'Tuesday', income, 0))
+ SUM(DECODE(day of the week, 'Wednesday', income, 0))
+ SUM(DECODE(day of the week, 'Thursday', income, 0))
+ SUM(DECODE(day of the week, 'Friday', income, 0))
+ SUM(DECODE(day of the week, 'Saturday', income, 0))
+ SUM(DECODE(day of the week, 'Sunday', income, 0)) AS TOTAL
Third model: Pivot desk with whole columns and rows and different totals
For instance, for instance an ice cream proprietor needs so as to add one other column to the pivot desk they supply that reveals the whole variety of purchases of every ice cream taste. No drawback, you need to use the identical idea so as to add one other “Complete” column.
SELECT NVL(ice cream taste, 'TOTAL') "ICE CREAM FLAVOR",
SUM(DECODE(day of the week, 'Monday', income, 0)) AS MONDAY, SUM(DECODE(day of the week, 'Tuesday', income, 0)) AS TUESDAY,
SUM(DECODE(day of the week, 'Wednesday', income, 0)) AS WEDNESDAY,
SUM(DECODE(day of the week, 'Thursday', income, 0)) AS THURSDAY,
SUM(DECODE(day of the week, 'Friday', income, 0)) AS FRIDAY,
SUM(DECODE(day of the week, 'Saturday', income, 0)) AS SATURDAY,
SUM(DECODE(day of the week, 'Sunday', income, 0)) AS SUNDAY,
SUM(income) AS TOTAL,
SUM(buy ID) "OTHER TOTAL"
FROM ice cream store dataset
WHERE date between final Monday and final Sunday
GROUP BY GROUPING SETS (ice cream taste, ());
