Hearing the Oracle

Home » Posts tagged 'unpivot'

Tag Archives: unpivot

Unpivoting Spreadsheets into Oracle

phingeThe premise is as follows: You’ve got useful data lying in an Excel or Google spreadsheet which you’d like translated into relational table(s). This is likely going to involve normalizing the denormalized spreadsheet data on the way into the database; you are going to end up with (and want) more rows in your table than the corresponding quantity within the spreadsheet. The SQL tool for this is the UNPIVOT keyword.     Continue…

Advertisements

More About PIVOT Queries

complex pivot query with both multiple aggregates and multiple pivots

complex pivot query with both multiple aggregate functions and multiple pivot columns

An earlier post introduced the PIVOT feature in Oracle’s 11g2 SQL, and described the motivation for pivoting. This one explores a few more complex ways to use pivot queries. We’ll look at:

         • Multiple Pivot Columns
         • Multiple Aggregate Functions

within the same SQL statement and we’ll see a few errors which commonly arise when building more complex pivot queries like these. Finally, we’ll consider: why would one have a need to UNPIVOT?     Continue…

Pivot Queries : Then & Now

Back around the era of Oracle 6 or 7, I had an intuition that a certain type of query could be written within the limits of SQL*Plus syntax, but wasn’t quite clear as to how. I was doing some ad-hoc DBA work for a development database once a week or so, and wished to get a handle on who was doing what how frequently in terms of object creation. I envisioned a tabular matrix tallying kinds of objects by user with the aim of watching the changes over time. After doing some legwork exploring the sys.obj$ table and hitting upon using DECODE to trap specific object/user pairs into desired columns, I came up with this:     Continue…