Optimize rows to columns conversion

oracleperformance

I have a big Oracle table (around 40 million rows) that looks like this:

ID  Name    Question Answer Reason      
3   Name1   1       Yes     blah blah
3   Name1   2       No      NA
3   Name1   3       No      NA
3   Name1   4       Yes     blah2
3   Name1   5       Yes     null
3   Name1   6       Yes     blah3
3   Name1   7       No      null
6   Name2   1       Yes     blah4444
6   Name2   2       No      null
6   Name2   3       Yes     blah3
6   Name2   4       NA      blah5
6   Name2   5       Yes     null
6   Name2   6       Yes     blah6
6   Name2   7       NA      null

I need one row per ID i.e. I will need to add columns for each question's answer (there are 7 questions per ID) and each question's reason. I need to make it look like this:

ID  Name    Q1     Q1-Reason    Q2    Q2-Reason   Q3    Q3-Reason    etc.
3   Name1   Yes    blah blah    No    null      
6   Name2   Yes    blah4444     No    null

My query currently looks like this:

select
   A.ID,A.NAME,B1.Q1,B1.Q1-REASON,B2.Q2,B2.Q2-REASON
from
   TABLENAME A
inner join
    (
    select distinct C1.ID,C1.ANSWER as Q1,C1.REASON as Q1-REASON
    from TABLENAME C1
    where C1.QUESTION=1
    ) B1 on B1.ID=A.ID
inner join
    (
    select distinct C2.ID,C2.ANSWER as Q2,C2.REASON as Q2-REASON
    from TABLENAME C2
    where C2.QUESTION=2
    ) B2 on B2.ID=A.ID
...
...

However, as the table is huge, this is taking a VERY long time to retrieve the data. Could someone suggest ways to optimize this query? Any help would be appreciated!

I'm on Oracle 10g and SQLDeveloper 4.0.2.15

Best Answer

Instead of using so many JOINs to get the result, you might get better performance pivoting the data using an aggregate function with a CASE expression. Oracle 10g doesn't have a PIVOT function so you'd have to use this type of query (aggregate/CASE) if you aren't going to use multiple JOINs on your table.

Since you have a limited number of questions (7), you can easily hard-code the query:

select
  id,
  name,
  max(case when question = 1 then Answer end) Q1_Answer,
  max(case when question = 1 then Reason end) Q1_Reason,
  max(case when question = 2 then Answer end) Q2_Answer,
  max(case when question = 2 then Reason end) Q2_Reason,
  max(case when question = 3 then Answer end) Q3_Answer,
  max(case when question = 3 then Reason end) Q3_Reason,
  max(case when question = 4 then Answer end) Q4_Answer,
  max(case when question = 4 then Reason end) Q4_Reason,
  max(case when question = 5 then Answer end) Q5_Answer,
  max(case when question = 5 then Reason end) Q5_Reason,
  max(case when question = 6 then Answer end) Q6_Answer,
  max(case when question = 6 then Reason end) Q6_Reason,
  max(case when question = 7 then Answer end) Q7_Answer,
  max(case when question = 7 then Reason end) Q7_Reason
from tablename
group by id, name;

See SQL Fiddle with Demo

Related Question