StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPivot rows to columns based on content in Oracle 10g PL/SQL
text
Body
copied!<p>I have a table in my database, <code>user_answers</code> that stores users answers to a series of questions, with rows; <code>user_id</code>, <code>question_id</code>, <code>answer_id</code> and <code>text_entry</code>. Question text and answer text (if any) are stored in lookup tables. There are three types of questions, single-answer questions, multiple-answer questions and text-entry answer questions. So a single user might have entries like the following in the <code>user_answers</code> table:</p> <pre><code>user_id question_id answer_id text_entry ------- ----------- --------- ---------- 123 100 1010 (null) 123 200 2010 (null) 123 200 2030 (null) 123 300 3000 "code 789" </code></pre> <p>Lets say the <code>questions_text</code> table has:</p> <pre><code>question_id text ----------- ------------- 100 "Gender" 200 "Interests" 300 "Your code" </code></pre> <p>and the <code>answers_text</code> table has:</p> <pre><code>answer_id text --------- ----------- 1010 "Female" 1020 "Male" 2010 "Sports" 2020 "Computers" 2030 "Movies" 3000 (null) </code></pre> <p>I want to extract the data into a csv with one line per user_id showing the answers, something like this:</p> <pre><code>User,Gender,Sports,Computers,Movies,Code 123,Female,1,0,1,code 789 </code></pre> <p>I know how to generate the CSV file via SQLPlus (I only have access to the DB via SQLPlus for reasons beyond my control...) but I don't know how to generate the PL/SQL statement. </p> <p>In PL/SQL I know I can generate a pivot of the Gender question by doing</p> <pre><code>SELECT user_id || ',' || MIN(DECODE(question_id, '100', (SELECT text FROM answers_text where answer_id = answer_text.answer_id))) FROM user_answers GROUP BY user_id ORDER BY user_id ; </code></pre> <p>(I'm not an SQL guy, so this is copied off the internets!)</p> <p>This code is (at least as far as my testing is telling me) good for the single-answer questions but will not work on multiple-answer or text-entry type questions.</p> <p>I saw some stuff online about using the <code>case</code> statement in PL/SQL like so:</p> <pre><code>MIN(CASE WHEN question_id = '200' AND answer_id = '2010' THEN '1' ELSE '0' END) </code></pre> <p>...but I can't figure out how to get the answers into columns. And all the SO questions I can find that might be related are sql-server specific.</p> <p>Is there a way to generate my desired output from a single PL/SQL statement? Preferably written in a way that does not depend on the data in the tables as we have a number of databases that this might need to be run on.</p>

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