Thanks for the question, Awanish.
Asked: October 07, 2018 - 11:36 am UTC
Last updated: October 08, 2018 - 1:11 am UTC
Version: 11g
Viewed 1000+ times
You Asked
I have a question table having 1 record.
create table q_text_t (q_id number,q_text varchar2(100));
insert into q_text_t values(1,'What is the capital of India?');
I have another answer table having 4 answers to the corresponding question.
create table a_text_t (q_id number,a_id number,a_text varchar2(30));
insert into a_text_t values(1,1,'KOlkata');
insert into a_text_t values(1,2,'New Delhi');
insert into a_text_t values(1,3,'Mumbai');
insert into a_text_t values(1,4,'Chennai');
Now I have another final exam table and want to insert a single record per question from the above table through PL/SQL code.
create table exam_t (q_id number,q_text varchar2(100),a1_text varchar2(30),a2_text varchar2(30),a3_text varchar2(30),a4_text varchar2(30));
It should have data like
1,'What is teh capital of India?','KOlkata','New Delhi',MUmbai, Chennai)
and Connor said...
A pivot takes care of that easily
SQL> create table q_text_t (q_id number,q_text varchar2(100));
Table created.
SQL> insert into q_text_t values(1,'What is the capital of India?');
1 row created.
SQL>
SQL> create table a_text_t (q_id number,a_id number,a_text varchar2(30));
Table created.
SQL>
SQL> insert into a_text_t values(1,1,'KOlkata');
1 row created.
SQL> insert into a_text_t values(1,2,'New Delhi');
1 row created.
SQL> insert into a_text_t values(1,3,'Mumbai');
1 row created.
SQL> insert into a_text_t values(1,4,'Chennai');
1 row created.
SQL>
SQL> select *
2 from a_text_t
3 pivot (max(a_text) as answer for (a_id) in (1 as a1, 2 as a2, 3 as a3, 4 as a4));
Q_ID A1_ANSWER A2_ANSWER A3_ANSWER A4_ANSWER
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------
1 KOlkata New Delhi Mumbai Chennai