view counter

Oracle PIVOT

Thanks to Scott Wesley for this story

A common requirement for queries is to turn rows into columns, or the other way around.

view counter

In Excel, we can do this using TRANSPOSE, a bit of patience & know-how, and ctrl+shift+enter.

In Oracle, if we have aggregate data displaying months by row, the old way was to use a bunch of DECODEs (or similar)

SELECT t.name
,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'jan',1,0)) jan
,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'feb',1,0)) feb
,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'mar',1,0)) mar
,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'apr',1,0)) apr

Read the entire article at its source

view counter