根据 Oracle 10g PL/SQL 中的内容将行转换为列

Question

我的数据库中有一个表 user_answers，它存储用户对一系列问题的回答，其中包含行； user_id、question_id、answer_id 和 text_entry。问题文本和答案文本（如果有）存储在查找表中。问题分为单选题、多选题和文字输入题三种。因此，单个用户在 user_answers 表中可能具有如下所示的条目：

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"

假设 questions_text 表具有：

question_id         text
-----------    -------------
    100           "Gender"
    200         "Interests"
    300         "Your code"

并且 answers_text 表具有：

answer_id       text
---------    -----------
   1010       "Female"
   1020        "Male"
   2010       "Sports"
   2020      "Computers"
   2030       "Movies"
   3000        (null)

I想要将数据提取到 csv 中，每个 user_id 一行显示答案，如下所示：

User,Gender,Sports,Computers,Movies,Code
123,Female,1,0,1,code 789

我知道如何通过 SQLPlus 生成 CSV 文件（出于我无法控制的原因，我只能通过 SQLPlus 访问数据库...）但我不知道如何生成 PL/SQL 语句。

在 PL/SQL 中，我知道我可以通过执行以下操作来生成性别问题的枢轴

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
;

（我不是 SQL 人员，所以这是从互联网上复制的！）

这段代码是（至少据我的测试告诉我）适用于单答案问题，但不适用于多答案或文本输入类型的问题。

我在网上看到一些关于在 PL/SQL 中使用 case 语句的内容，如下所示：

MIN(CASE WHEN question_id = '200' AND answer_id = '2010' THEN '1' ELSE '0' END)

...但我不知道如何将答案放入列中。我能找到的所有可能相关的问题都是 sql-server 特定的。

有没有办法从单个 PL/SQL 语句生成我想要的输出？最好以不依赖于表中数据的方式编写，因为我们可能需要在许多数据库上运行。

Answer 1

为了完成您正在寻找的内容（而不是特定于这些数据），我相信您将需要在表中添加一些额外的字段。例如，您需要知道哪些问题是单选题、多选题和文本输入题，而无需查看数据。您还需要知道您的多重答案问题可能有哪些答案，而无需链接数据。从那里，您可以循环遍历有关每个问题/答案组合的元信息，并自行构建一个要运行的查询，该查询将以您所需的格式返回数据。像这样的东西：

/* 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;

Answer 2

列数未知的查询充其量也是有问题的。您真的不知道数据会是什么样子吗？您可能想看看这个 向 Tom 询问可能会帮助您获得所需结果的软件包。