# Pivot – You are doing it wrong

Suppose you have a table like this:

```drop table t_;

create table t_ (
nm Varchar2(20),
pr Char    ( 7),
vl Number
);

insert into t_ values ('company 1','2003-06', 10);
insert into t_ values ('company 1','2003-07', 29);
insert into t_ values ('company 1','2003-08', 39);
insert into t_ values ('company 1','2003-09', 41);
insert into t_ values ('company 1','2003-10', 22);

insert into t_ values ('company 2','2003-06', 13);
insert into t_ values ('company 2','2003-07', 17);
insert into t_ values ('company 2','2003-08', 61);
insert into t_ values ('company 2','2003-09', 55);
insert into t_ values ('company 2','2003-10', 71);

insert into t_ values ('company 3','2003-06', 33);
insert into t_ values ('company 3','2003-07', 18);
insert into t_ values ('company 3','2003-08', 27);
insert into t_ values ('company 3','2003-09',  5);
insert into t_ values ('company 3','2003-10', 32);
```

(Thanks to René Nyffenegger for the helpful create script)

And you want this output:

```NM                          JUL        AUG        SEP
-------------------- ---------- ---------- ----------
company 2                    17         61         55
company 3                    18         27          5
company 1                    29         39         41
```

If you are using 10g or older versions, you can use Tom Kyte’s decode trick. In 11g, we have an official keyword to solve this problem.

What you should never, ever do, is this:

```  select
t1.nm,
t1.vl jul,
t2.vl aug,
t3.vl sep
from
t_ t1,
t_ t2,
t_ t3
where 1=1
and t1.nm=t2.nm
and t2.nm=t3.nm
and t1.pr='2003-07'
and t2.pr='2003-08'
and t3.pr='2003-09'
group by t1.nm,
t1.vl,
t2.vl,
t3.vl
```

And if you do this, and if t_ has more than 15 rows, and if you actually need more than 3 columns, don’t be surprised if the performance may be slightly disappointing.

### 13 Comments on “Pivot – You are doing it wrong”

1. Luiz Vaz says:

Can you tell what keyword you mean?
If it is the obvius PIVOT, can you draw how can be the best way using the same sample data?

I’m just guessing, thanks!

2. Hi Chen,
I find the Tom Kyte utterly hard to read, to understand, to maintain and to tune.

“Never say never, never say always”, Tom always said

``` SQL> drop table lsc_t;```

``` Table dropped. Elapsed: 00:00:00.67 SQL> SQL> create table lsc_t ( 2 nm Varchar2(20), 3 pr date, 4 vl Number, 5 primary key (nm,pr) 6 ); Table created. Elapsed: 00:00:00.03 SQL> SQL> insert into lsc_t 2 select nm,pr,trunc(dbms_random.value(1,100)) 3 from 4 (select add_months(date '1900-01-01',rownum) pr from all_objects) 5 cross join 6 (select 'customer '||rownum nm from all_objects where rownum SQL> commit; Commit complete. Elapsed: 00:00:00.01 SQL> SQL> SQL> select 2 t1.nm, 3 t1.vl jul, 4 t2.vl aug, 5 t3.vl sep 6 from 7 lsc_t t1, 8 lsc_t t2, 9 lsc_t t3 10 where 1=1 11 and t1.nm=t2.nm 12 and t2.nm=t3.nm 13 and t1.pr=date '2003-07-01' 14 and t2.pr=date '2003-08-01' 15 and t3.pr=date '2003-09-01' 16 group by t1.nm, 17 t1.vl, 18 t2.vl, 19 t3.vl; NM JUL AUG SEP -------------------- ---------- ---------- ---------- customer 1 39 25 29 customer 2 85 69 83 customer 3 53 70 59 Elapsed: 00:00:00.04 SQL> SQL> SQL> select nm, 2 max(decode( pr, date '2003-07-01', vl )) jul, 3 max(decode( pr, date '2003-08-01', vl )) aug, 4 max(decode( pr, date '2003-09-01', vl )) sep 5 from lsc_t 6 group by nm; NM JUL AUG SEP -------------------- ---------- ---------- ---------- customer 1 39 25 29 customer 2 85 69 83 customer 3 53 70 59 ```

```Elapsed: 00:00:00.15 ```

What’s wrong with selecting the same table thrice? It is occasionaly faster and way easier to maintain

In a book with an amazing picture on page 302 I wrote about PIVOT, but nobody is using 11g around… Also I am not use it is much different from Tom syntax. And also, it is not equivalent

BTW, to tune up my query, simply remove the group by 🙂

``` SQL> select 2 t1.nm, 3 t1.vl jul, 4 t2.vl aug, 5 t3.vl sep 6 from 7 lsc_t t1, 8 lsc_t t2, 9 lsc_t t3 10 where 1=1 11 and t1.nm=t2.nm 12 and t2.nm=t3.nm 13 and t1.pr=date '2003-07-01' 14 and t2.pr=date '2003-08-01' 15 and t3.pr=date '2003-09-01';```

``` ```

```NM JUL AUG SEP -------------------- ---------- ---------- ---------- customer 1 39 25 29 customer 2 85 69 83 customer 3 53 70 59 ```

3. (of course, wordpress sucks, my < just truncated my code…)

4. PS: do not blame me for storing dates in date…

5. prodlife says:

Hi Luiz Vaz,

Indeed I was talking about PIVOT. Here’s how I use pivot on this example:

select * from
( SELECT nm, pr, vl FROM t_)
PIVOT
(max(vl) FOR pr IN (‘2003-07’ AS JUL, ‘2003-08’ AS AUG, ‘2003-09’ AS SEP))

6. prodlife says:

Laurent,

Quiet a long comment…

1. Tom is right. I shouldn’t have said never. Maybe I should have said “Please don’t do it in code that I have to look at later”.

2. I forgot to mention that in the original code the date was not indexed, and it was a char… (varchar actually). I also forgot to mention that t_ was actually a complicated inline view that was copy pasted 11 times in the query.

3. And of course, the original did contain group by, to avoid showing the duplicates that other parts of the 18 table join created. Kind of like “distinct”.

In short, my example doesn’t do justice to the headache I went through yesterday. But I still think it is a good example of what not to do unless you are Laurent Schneider.
You get special exception to run whatever you want. If all my developers were that sexy, I’d let them run whatever they wanted too 😉

7. Chen,

You saved my day !

Actually, in my former CS team I wrote something with max(decode()) KEEP … and after I left, they added some columns and ended up with rewriting with multiple joins. They asked me how to tune it. My first tuning hint was : “Removed that stupid GROUP BY clause”. I suggested that if the java guys have a maintainable query which does not look ugly, it is probably as good as a “bullet” query that will be misused next time they add a column.

YMMV

8. Sokrates says:

“Never say never, never say always”, Tom always said

right, but I also heard him saying
“never use ‘when others then null;'” (which I agree on), so himself sometimes says never

nice blog by the way

9. Sokrates says:

picture on p. 302 is really amazing, indeed !

10. Chen Shapira says:

I’ll admit to using “when others then null” on occasion.
Usually right after “drop table t”.
I wish Oracle had “drop if exists t”, the same way it has “create or replace”.

11. When others then null is really ugly… I told my editor to NOT PUT the code in the book, well, blame me now!!!

``` Prompt drop TABLE TLS_EXPOSURE_FACTS_TMP; declare TABLE_NOT_EXISTS exception;pragma exception_init(TABLE_NOT_EXISTS,-942);begin execute immediate ' DROP TABLE TLS_EXPOSURE_FACTS_TMP CASCADE CONSTRAINTS ';exception when TABLE_NOT_EXISTS then null; end; / ```

12. prodlife says:

Thanks for the snippet, dear.
I just thought that declaring a pragma and all is a bit of an overkill if I just want to drop a table!

13. DDL,

if you just want to drop that table:

DROP TABLE t;