Oracle sql 查询：动态填充的数据透视表（提前）

我需要展平一个表，但这里是棘手的部分，列是动态的，并且当添加包含新 ID 的新记录时查询应该可以工作。

这是我的工作查询（简化了 IN 这实际上是数百个值）：

SELECT *
FROM   (select qv.respnr, sq.question_id, qv.question_value
from survey_question sq, question_values qv
where qv.question_id = sq.question_id
and sq.survey_id = 1
order by qv.respnr, page, ranked)
PIVOT (
MAX(question_value)        --<-- pivot_clause
FOR question_id          --<-- pivot_for_clause
IN (346 as c346,347 as c347)
)

我想更换IN (346 as c346,347 as c347)像这样的东西：

SELECT mq.question_id
  FROM meta_question mq, survey_question sq2
 WHERE sq2.survey_id = 1
   AND mq.question_id = sq2.question_id
 ORDER BY page, ranked

知道如何做到这一点吗？

我注意到 IN 不能简单地用 select 语句来归档，所以这不起作用：

IN (SELECT mq.question_id
      FROM meta_question mq, survey_question sq2
     WHERE sq2.survey_id = 1
       AND mq.question_id = sq2.question_id
     ORDER BY page, ranked)

SQL中所有列的名称和类型需要在编译时知道，这里你必须使用动态SQL http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#CACDDACH因为您希望改变列数取决于数据.

如果您使用 PL/SQL，则可以使用参考光标 http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#i13057:

DECLARE
   l_rc            SYS_REFCURSOR;
   l_dynamic_query VARCHAR2(32000);
BEGIN
   FOR cc IN (SELECT mq.question_id
                FROM meta_question mq, survey_question sq2
               WHERE sq2.survey_id = 1
                 AND mq.question_id = sq2.question_id
               ORDER BY page, ranked) LOOP
      -- build dynamic query here
   END LOOP;
   OPEN l_rc FOR '
      SELECT *
        FROM (SELECT qv.respnr, sq.question_id, qv.question_value
                FROM survey_question sq, question_values qv
               WHERE qv.question_id = sq.question_id
                 AND sq.survey_id = 1
               ORDER BY qv.respnr, page, ranked) 
               PIVOT ( MAX (question_value) --<-- pivot_clause
                       FOR question_id --<-- pivot_for_clause
                        IN (' || l_dynamic_query || ')
                      )';
    -- process l_rc (LOOP..FETCH..CLOSE)
END;

您还可以使用DBMS_SQL http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#BHCIBJBG.