Recipe14.3.Transposing a Result Set Using Oracle s MODEL Clause

Recipe 14.3. Transposing a Result Set Using Oracle's MODEL Clause

Problem

Like the fist recipe in this chapter, you wish to find an alternative to the traditional pivoting techniques you've seen already. You want to try your hand at Oracle's MODEL clause. Unlike SQL Server's PIVOT operator, Oracle's MODEL clause does not exist to transpose result sets; as a matter of fact, it would be quite accurate to say the application of the MODEL clause for pivoting would be a misuse and clearly not what the MODEL clause was intended for. Nevertheless, the MODEL clause provides for an interesting approach to a common problem. For this particular problem, you want to transform the following result set from this:

  select deptno, count(*) cnt   from emp  group by deptno DEPTNO        CNT ------ ----------     10          3     20          5     30          6 

to this:

        D10        D20        D30 ---------- ---------- ----------          3          5          6 

Solution

Use aggregation and CASE expressions in the MODEL clause just as you would use them if pivoting with traditional techniques. The main difference in this case is that you use arrays to store the values of the aggregation and return the arrays in the result set:

 select max(d10) d10,        max(d20) d20,        max(d30) d30   from ( select d10,d20,d30   from ( select deptno, count(*) cnt from emp group by deptno )  model   dimension by(deptno d)    measures(deptno, cnt d10, cnt d20, cnt d30)    rules(      d10[any] = case when deptno[cv()]=10 then d10[cv()] else 0 end,      d20[any] = case when deptno[cv()]=20 then d20[cv()] else 0 end,      d30[any] = case when deptno[cv()]=30 then d30[cv()] else 0 end   )   ) 

Discussion

The MODEL clause is an extremely useful and powerful addition to the Oracle SQL toolbox. Once you begin working with MODEL you'll notice helpful features such as iteration, array access to row values, the ability to "upsert" rows into a result set, and the ability to build reference models. You'll quickly see that this recipe doesn't take advantage of any of the cool features the MODEL clause offers, but it's nice to be able to look at a problem from multiple angles and use different features in unexpected ways (if for no other reason than to learn where certain features are more useful than others).

The first step to understanding the solution is to examine the inline view in the FROM clause. The inline view simply counts the number of employees in each DEPTNO in table EMP. The results are shown below:

  select deptno, count(*) cnt   from emp  group by deptno DEPTNO        CNT ------ ----------     10          3     20          5     30          6 

This result set is what is given to MODEL to work with. Examining the MODEL clause, you see three subclauses that stand out: DIMENSION BY, MEASURES, and RULES. Let's start with MEASURES.

The items in the MEASURES list are simply the arrays you are declaring for this query. The query uses four arrays: DEPTNO, D10, D20, and D30. Like columns in a SELECT list, arrays in the MEASURES list can have aliases. As you can see, three of the four arrays are actually CNT from the inline view.

If the MEASURES list contains our arrays, then the items in the DIMENSION BY subclause are the array indices. Consider this: array D10 is simply an alias for CNT. If you look at the result set for the inline view above, you'll see that CNT has three values: 3, 5, and 6. When you create an array of CNT, you are creating an array with three elements, namely, the three integers 3, 5, and 6. Now, how do you access these values from the array individually? You use the array index. The index, defined in the DIMENSION BY subclause, has the values of 10, 20, and 30 (from the result set above). So, for example, the following expression:

 d10[10] 

would evaluate to 3, as you are accessing the value for CNT in array D10 for DEPTNO 10 (which is 3).

Because each of the three arrays (D10, D20, D30) contain the values from CNT, all three of them have the same results. How then do we get the proper count into the correct array? Enter the RULES subclause. If you look at the result set for the inline view shown earlier, you'll see that the values for DEPTNO are 10, 20, and 30. The expressions involving CASE in the RULES clause simply evaluate each value in the DEPTNO array:

• If the value is 10, store the CNT for DEPTNO 10 in D10[10] else store 0.

• If the value is 20, store the CNT for DEPTNO 20 in D20[20] else store 0.

• If the value is 30, store the CNT for DEPTNO 30 in D30[30] else store 0.

If you find yourself feeling a bit like Alice tumbling down the rabbit hole, don't worry; just stop and execute what's been discussed thus far. The following result set represents what has been discussed. Sometimes it's easier to read a bit, look at the code that actually performs what you just read, then go back and read it again. The following is quite simple once you see it in action:

  select deptno, d10,d20,d30   from ( select deptno, count(*) cnt from emp group by deptno )  model   dimension by(deptno d)    measures(deptno, cnt d10, cnt d20, cnt d30)    rules(      d10[any] = case when deptno[cv( )]=10 then d10[cv( )] else 0 end,      d20[any] = case when deptno[cv( )]=20 then d20[cv( )] else 0 end,      d30[any] = case when deptno[cv( )]=30 then d30[cv( )] else 0 end   )   DEPTNO        D10        D20        D30   ------ ---------- ---------- ----------       10          3          0          0       20          0          5          0       30          0          0          6 

As you can see, the RULES subclause is what changed the values in each array. If you are still not catching on, simply execute the same query but comment out the expressions in the RULES subclase:

  select deptno, d10,d20,d30   from ( select deptno, count(*) cnt from emp group by deptno )  model   dimension by(deptno d)    measures(deptno, cnt d10, cnt d20, cnt d30)    rules(     /*      d10[any] = case when deptno[cv( )]=10 then d10[cv( )] else 0 end,      d20[any] = case when deptno[cv( )]=20 then d20[cv( )] else 0 end,      d30[any] = case when deptno[cv( )]=30 then d30[cv( )] else 0 end     */   )   DEPTNO        D10        D20        D30   ------ ---------- ---------- ----------       10          3          3          3       20          5          5          5       30          6          6          6 

It should be clear now that the result set from the MODEL clause is the same as the inline view, except that the COUNT operation is aliased D10, D20, and D30. The query below proves this:

  select deptno, count(*) d10, count(*) d20, count(*) d30   from emp  group by deptno  DEPTNO        D10        D20        D30  ------ ---------- ---------- ----------      10          3          3          3      20          5          5          5      30          6          6          6 

So, all the MODEL clause did was to take the values for DEPTNO and CNT, put them into arrays, and then make sure that each array represents a single DEPTNO. At this point, arrays D10, D20, and D30 each have a single non-zero value representing the CNT for a given DEPTNO. The result set is already transposed, and all that is left is to use the aggregate function MAX (you could have used MIN or SUM; it would make no difference in this case) to return only one row:

  select max(d10) d10,        max(d20) d20,        max(d30) d30   from ( select d10,d20,d30   from ( select deptno, count(*) cnt from emp group by deptno )  model   dimension by(deptno d)    measures(deptno, cnt d10, cnt d20, cnt d30)    rules(      d10[any] = case when deptno[cv()]=10 then d10[cv()] else 0 end,      d20[any] = case when deptno[cv()]=20 then d20[cv()] else 0 end,      d30[any] = case when deptno[cv()]=30 then d30[cv()] else 0 end   )   )          D10        D20        D30   ---------- ---------- ----------            3          5          6