title: Pivot rows to columns based on content in Oracle 10g PL/SQL
body: <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>