Monday, May 16, 2011

Oracle: To convert a row into a column (CROSS-JOIN)

This afternoon, while I am doing some Jasper report stuff, I am puzzled by a SQL query that which takes quite a long time to execute. As the time goes, Jasper will easily get a timeout and a blank screen is waiting for me after retrieving data for about 120 seconds.

As it is not appropriate to use the original data table as the example to describe here, I tried to make up a similar scenario which should explain the cause and the solution sufficiently.

Given a table A, with five columns -- Primary Key (pk), Statistics 1A (s1a), Statistics 1B (s1b), Statistics 2A(s2a), Statistics 2B(s2b), we need to select the data out into a form that looks like -- Primary Key (pk), Statistics A(s1a or s2a), Statistics B(s2b or s2b). Essentially, it is trying to convert a row into one column but two rows.  Someone may prefer to call it pivot query by the way.

Initially, naively I think the most easiest and intuitive way to do it is by two union queries. That is
select pk, s1a, s1b from table where *** union select pk, s2a, s2b from table where ***.
When the data set is small, and it only depends on the single table, it is fine. However, in my case, the data set is huge, and the union will not be two but eight, and more importantly every union section will consists of another 6 inner join tables. That's the reason why Jasper cannot retrieve what it needs on time.

In this link, as suggested by Scott, introduced a very smart way to handle this issue. What it uses is called: cross join. So basically, we use the existing table to cross join with the different type/criteria to return the expected result.

select
   pk,
   case
      when ite = 's1' then s1a
      when ite = 's2' then s2a
   end as sa
   case
      when ite = 's1' then s1b
      when ite = 's2' then s2b
   end as sb
 from
   (
       select pivoter.ite,
           s1a, s1b, s2a, s2b
       from
           table
           cross join (
               select 's1' as ite from dual
               union all
               select 's2' as ite from dual
           ) pivotel
   )

By using the above code, the problem will be solved by the cross join union columns.

1 comment:

  1. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this "Devops Training in Bangalore"

    ReplyDelete