Abstract Confusions

Complexity is not a cause of confusion. It is a result of it.

LISTAGG and PIVOT functions in Oracle 11g R2

Analytic functions are first introduced in Oracle 8i. They have got few functions in new release. The first function which I will discuss here will be LISTAGG. It creates a delimited list within a group. Some times you need to toil hard for this. Most of the times, grouping by and converting columns to rows.

With Oracle 11g R2, all you need is the following.

SQL> select deptno,
  2         listagg( ename, '; ' )
  3         within group
  4         (order by ename) enames
  5     from hr.employees
  6    group by deptno
  7    order by deptno
  8   /

  DEPTNO   ENAMES
---------  --------------------
  10       CLARK; KING; MILLER
  20       ADAMS; FORD; JONES;
           SCOTT; SMITH
  30       ALLEN; BLAKE;
           JAMES; MARTIN;
           TURNER; WARD

You can decide what to append with. The syntax is LISTAGG(column, character).

Prior versions you have to use SYS_CONNECT_BY_PATH for this. The new one looks neat though!

Pivot Function

Pivot function is an important function in data warehouses. Prior to this version, people used a roundabout version to achieve this feature. Check here for the various methods. Most of the time, you want the data to be presented as a tabular form. Consider the following ‘sales’ table.

Product Channel Amt Sold
CDROM Direct 2000
CDROM Market 45000
CDROM Internal 300
CDROM Distributor 3400000
Key Board Direct 100
Key Board Market 6500
Key Board Internal 200
Key Board Distributor 7845000

Now, if you want to pivot it for the channels, the command would be as the following.

SQL> select *
  2	from
  3      (select product
  4	      , channel
  5	      , amt_sold
  6	    from sales_view ) s
  7     PIVOT (sum(amt_sold)
  8	     for channel in (‘DIRECT’, ‘MARKET’
  9			   , ‘INTERNAL’, ‘DISTRIBUTOR’)
 10	     )
 11   /

PRODUCT     DIRECT     MARKET     INTERNAL     DISTRIBUTOR
----------- ---------- ---------- ------------ -----------
CDROM       2000       450000     300          3400000
Key Board   100        6500       200          784500

The same way, you can pivot it using several attributes. It some cases, if you want to UNPIVOT a PIVOTED table, you can use UNPIVOT (AMT_SOLD, FOR CHANNEL IN (‘DIRECT’, ‘MARKET’, ‘INTERNAL’, ‘DISTRIBUTOR’)).

5 responses to “LISTAGG and PIVOT functions in Oracle 11g R2

  1. Pingback: Twitted by guruparan18

  2. Sambath December 2, 2009 at 8:55 AM

    it’s pretty useful.Thanks for the post.

  3. Pingback: LISTAGG and PIVOT functions in Oracle 11g R2 « Abstract Confusions « Oracle Applications – Business & Technology

  4. Gaitteniaxy January 1, 2010 at 6:03 PM

    Thanks for the informations!!

  5. Pingback: 11gR2 New Feature – ListAgg analytic function « The Oracle Database Blog

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: