The 11g pivot query and the group by clause


There has been this long awaited pivot query feature available since 11g that saves us time in writing those infamous decode / group by queries (see expert one-on-one from Tom Kyte or the web for examples) to flip (grouped by) leading row values to column names.

Searching the web for application examples, however, does only reveal this emp-table stuff, e.g. on http://orafaq.com/wiki/PIVOT, that still contains the well known group by with the base query.

SELECT *
  FROM (SELECT job, deptno, sum(sal) sal FROM emp GROUP BY job, deptno)
         PIVOT ( sum(sal) FOR deptno IN (10, 20, 30, 40) );

JOB               10         20         30         40
--------- ---------- ---------- ---------- ----------
CLERK           1300       1900        950
SALESMAN                              5600
PRESIDENT       5000
MANAGER         2450       2975       2850
ANALYST                    6000

Jep so, what I wanted to achive was a simple flip of rows of status values and counts into columns of status names and count values based on some data like the following.

select status, count(*) from feed_queue group by status

STATUS	COUNT(*)
----------------
WAIT	   751
WORK	  2000
LOST	    28
TODO	513761
DONE	 12351

My first attempt went out like this (do not laugh at me … I’m only honest).

select * from (select status, count(*) from feed_queue group by status)
  pivot (count(*) for status in ('TODO', 'WORK', 'WAIT', 'DONE', 'LOST', 'ERRR'))

COUNT(*)       'TODO' 'WORK'  'WAIT'  'DONE'  'LOST'  'ERRR'
------------------------------------------------------------
29              0       0       0       0       1       0
2000            0       1       0       0       0       0
446             0       0       1       0       0       0
511761          1       0       0       0       0       0
14655           0       0       0       1       0       0

Kränk, no? It seems that there is a second thought nessecary to employ the new feature accordingly. Without going into the details too much, one has to catch that the pivot expression also does a (hidden) group by on the data such that the count values had been grouped twice. The accurate statement is as follows, expection the pivot to do its group by job under the covers.

select * from (select status from feed_queue)
  pivot (count(*) for status in ('TODO', 'WORK', 'WAIT', 'DONE', 'LOST', 'ERRR'))

'TODO'  'WORK'  'WAIT'  'DONE'  'LOST'  'ERRR'
----------------------------------------------
509761  2000    1612    15480   38      0

There is another aspect worth mentioning. That is, since the (new) column names become system generated on values, one has to care to adhere to the sql identifier rules (see dbms_assert for a discussion). Unfortunately, dbms_assert calls cannot be placed into the inlist of status names.

And finally, going to execute this sql within pl/sql takes another syntax lesson to learn, for me at least. We have a character value constant syntax within an identifier experession.

  for cur in (
    select * from (select status from feed_queue)
      pivot (count(*) for status in ('TODO', 'WORK', 'WAIT', 'DONE', 'LOST', 'ERRR')) ) loop
    fplogg.cout(v_module, 'OK', 'Stop population the ontos feed, current status is TODO: ' ||
      cur."'TODO'" || ', WORK: ' || cur."'WORK'" || ', WAIT: ' || cur."'WAIT'" || ', DONE: ' ||
      cur."'DONE'" || ', LOST: ' || cur."'LOST'" || ', ERRR: ' || cur."'ERRR'");
  end loop;

Have fun!

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s