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.

      when ite = 's1' then s1a
      when ite = 's2' then s2a
   end as sa
      when ite = 's1' then s1b
      when ite = 's2' then s2b
   end as sb
       select pivoter.ite,
           s1a, s1b, s2a, s2b
           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. 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"

    1. The effectiveness of IEEE Project Domains depends very much on the situation in which they are applied. In order to further improve IEEE Final Year Project Domains practices we need to explicitly describe and utilise our knowledge about software domains of software engineering Final Year Project Domains for CSE technologies. This paper suggests a modelling formalism for supporting systematic reuse of software engineering technologies during planning of software projects and improvement programmes in Final Year Projects for CSE.

      Software management seeks for decision support to identify technologies like JavaScript that meet best the goals and characteristics of a software project or improvement programme. JavaScript Training in Chennai Accessible experiences and repositories that effectively guide that technology selection are still lacking.

      Aim of technology domain analysis is to describe the class of context situations (e.g., kinds of JavaScript software projects) in which a software engineering technology JavaScript Training in Chennai can be applied successfully

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training


  3. Go with us on the online casino BGAOC and most likely let's win. perfect gambling websites Do not miss the gambling chance to become a great player.

  4. Informative post indeed, I’ve being in and out reading posts regularly and I see alot of engaging people sharing things and majority of the shared information is very valuable and so, here’s my fine read.
    click here for more details
    click here for new registration
    click here to login
    click here 4th std
    click here icon

  5. Great blog thanks for sharing Consistent and relevant content is the best ways to drive in massive amounts of traffic to your website through Search Engines. Adhuntt Media’s content creation team has got everything covered for you - from high-grade blogs that engage the reader and hit all the sweet SEO spots, unique design posts for your social media feed and professional videos for video marketing.
    digital marketing company in chennai

  6. Nice blog thanks for sharing Re-decorate your messy lawn and fix up your boring old garden with Karuna Nursery Gardens. We offer speciality Garden maintenance services in Chennai. It’s time you made a difference to your backyard.
    plant nursery in chennai

  7. Excellent blog thanks for sharing Run your salon business successfully by tying up with the best beauty shop in Chennai - The Pixies Beauty Shop. With tons of prestigious brands to choose from, and amazing offers we’ll have you amazed.
    Cosmetics Shop in Chennai

  8. And also thanks for sharing this informative. Keep it Sir and I am waiting for your next post on your site blog.

    Best Training Institute in Bangalore BTM. My Class Training Bangalore training center for certified course, learning on Software Training Course by expert faculties, also provides job placement for fresher, experience job seekers.
    Software Training Institute in Bangalore

  9. Thank you so much for the great and very beneficial stuff that you have shared with the world.

    Learn Hadoop Training from the Industry Experts we bridge the gap between the need of the industry. Softgen Infotech provide the Best Hadoop Training in Bangalore with 100% Placement Assistance. Book a Free Demo Today.
    Big Data Analytics Training in Bangalore
    Tableau Training in Bangalore
    Data Science Training in Bangalore
    Workday Training in Bangalore