Oracle sql查询：透视表用动态填充（高级） [英] Oracle sql query: pivot table with dynamicly filled for in (advanced)

问题描述

我需要平铺一张桌子，但是这里是棘手的部分，这个clomun是动态的，当添加新的记录时，查询应该可以工作，包含一个新的ID。

I need to flatten an table, but here si the tricky part, the clomuns are dynamic and the query should work when new records are added containing a new ID.

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

This is my working query (simplified the IN this is really hundreds of values):

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语句的fileld，所以这不起作用：

I've noticed the IN can't be simply fileld with a select statement, so this doesn't work:

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 ，因为您希望列数根据数据更改。

The name and type of all columns in SQL needs to be known at compile time, here you will have to use dynamic SQL since you want the number of columns to change depending upon the data.

如果您使用PL / SQL，可以使用 ref cursor ：

If you use PL/SQL, you can use a ref cursor:

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 。

You can also use DBMS_SQL.

这篇关于Oracle sql查询：透视表用动态填充（高级）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！