StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>To accomplish what you are looking for (and not be specific to this data) I believe you are going to need some extra fields in your tables. For example, you will need to know which questions are Single-Answer, Multi-Answer, and Text-Entry without having to look at the data. You will also need to know which answers are possible for your Multi-answer questions without having to link through the data. From there, you can loop through the meta information about each question / answer combination and build yourself a query to run that will return the data in your desired format. Something like:</p> <pre><code>/* Create Tables with Data - Note 2 new columns added to questions_text */ create table user_answers as select 123 user_id, 100 question_id, 1010 answer_id, null text_entry from dual union all select 123 user_id, 200 question_id, 2010 answer_id, null text_entry from dual union all select 123 user_id, 200 question_id, 2030 answer_id, null text_entry from dual union all select 123 user_id, 300 question_id, 3000 answer_id, 'code 789' text_entry from dual; create table questions_text as select 100 question_id, 'Gender' question_text, 'S' question_type, 1000 answer_set_id from dual union all select 200 question_id, 'Interests' question_text, 'M' question_type, 2000 answer_set_id from dual union all select 300 question_id, 'Your code' question_text, 'T' question_type, 3000 answer_set_id from dual; create table answers_text as select 1010 answer_id, 'Female' text, 1000 answer_set_id from dual union all select 1020 answer_id, 'Male' text, 1000 answer_set_id from dual union all select 2010 answer_id, 'Sports' text, 2000 answer_set_id from dual union all select 2020 answer_id, 'Computers' text, 2000 answer_set_id from dual union all select 2030 answer_id, 'Movies' text, 2000 answer_set_id from dual union all select 3000 answer_id, null text, 3000 answer_set_id from dual; /* PL/SQL for creating SQL statement to return data in desired format */ declare v_sql VARCHAR2(32767); begin v_sql := 'select ua.user_id "User",'; FOR question IN ( select question_id, question_text, question_type, answer_set_id from questions_text ) LOOP IF question.question_type = 'M' THEN FOR answer IN ( select answer_id, text from answers_text where answer_set_id = question.answer_set_id ) LOOP v_sql := v_sql||chr(10)||'max(case when ua.question_id = '||question.question_id||' and ua.answer_id = '||answer.answer_id||' then 1 else 0 end) "'||answer.text||'",'; END LOOP; ELSIF question.question_type = 'S' THEN v_sql := v_sql||chr(10)||'min(case when ua.question_id = '||question.question_id||' then at.text end) "'||question.question_text||'",'; ELSIF question.question_type = 'T' THEN v_sql := v_sql||chr(10)||'min(case when ua.question_id = '||question.question_id||' then ua.text_entry end) "'||question.question_text||'",'; END IF; END LOOP; v_sql := rtrim(v_sql,','); v_sql := v_sql||' from user_answers ua inner join questions_text qt on qt.question_id = ua.question_id inner join answers_text at on at.answer_id = ua.answer_id group by ua.user_id'; -- replace dbms_output with code to write file dbms_output.put_line(v_sql); END; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload