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 220.127.116.11