Advanced Use of Oracle DECODE CASE PIVOT UNPIVOT
The original SQL language focused on providing standard selection, projection, and sorting of data. Enhancements over the years allow SQL to deliver much more robust analysis of data. This webinar investigates how DECODE, CASE, PIVOT, and UNPIVOT provide these capabilities.
After a brief review, we will show how these features allow you to do range comparisons, create horizontal and vertical histograms, and flip tables on the side by converting rows into columns and columns into rows. These features are particularly useful to identify trends in data.
Audience: Oracle and APEX developers who want to improve their SQL skills. A basic foundation in SQL is required.
This is a multi-part tutorial. Part 1 is available now.
- Introduction to the Oracle PIVOT clause (and UNPIVOT) (11:46) (click on video below)
PIVOT allows you to “flip a table on its side”, i.e. Columns to Rows / Rows to Columns. Previously we did this with DECODE or CASE. As of Oracle 11g, Oracle Database includes the PIVOT clause (and UNPIVOT). Oracle expert Geoff Wiland from SkillBuilders will demonstrate PIVOT, UNPIVOT, including the use of aggregate functions.
- SQL CASE Expression (1:13)
Learn Oracle SQL searched CASE comparisons. Note that DECODE only supports equality comparisons, but searched CASE supports IN, BETWEEN, LIKE and scalar functions!
- How to Pivot (Flip) Tables with SQL CASE & DECODE (4:25)
Pivoting tables (columns to rows, rows to columns) is a common data analysis requirement. Learn how to accomplish this with Oracle SQL CASE and DECODE
Date: May 19, 2016
More lessons in this tutorial coming soon.
NOTE: Some corporate firewalls will not allow videos hosted by YouTube.