使用两个动态值在 oracle v10.2.0 上进行透视 [英] Pivotting on oracle v10.2.0 with two dynamic values

问题描述

我需要得到这样的查询结果:

I need to get a query result like this:

        |Person1                  |Person2                  |Person3                  |...
        ------------------------------------------------------------------------------------
Date1   |function(Person1Id,Date1)|function(Person2Id,Date1)|function(Person3Id,Date1)|...         
Date2   |function(Person1Id,Date2)|function(Person2Id,Date2)|function(Person3Id,Date2)|...
Date3   |function(Person1Id,Date3)|function(Person2Id,Date3)|function(Person3Id,Date3)|...
.
.
.

日期来自用户，而 PersonId 来自表格.我需要做的只是发送函数的 id 和日期并获得它的结果.由于我正在研究 oracle v10.2.0 透视不起作用并且编写案例......每个人的语句将不起作用，因为我正在获取的表中有很多人.

Dates are coming from the user and PersonIds are coming from a table. What I need to is just sending ids and dates o the function and get the result of it. Since I am working on oracle v10.2.0 pivoting does not work and writing case...when statements for each person will not work because there are lots of people in the table I am fetching.

感谢任何帮助.

推荐答案

您可以在 DB 版本 10g 中使用 Conditional Aggregation 例如

You can use Conditional Aggregation within DB version 10g such as

SELECT myDate,
       MAX(CASE WHEN PersonId=1 THEN myfunc(PersonId,myDate) END) AS Person1,
       MAX(CASE WHEN PersonId=2 THEN myfunc(PersonId,myDate) END) AS Person2,
       MAX(CASE WHEN PersonId=3 THEN myfunc(PersonId,myDate) END) AS Person3
  FROM t 
 GROUP BY myDate

更新: 然而，即使在 DB 10g 中，也存在一个选项，用于使用 SYS_REFCURSOR 进行动态旋转，例如.使用 PL/SQL 而不是仅使用 SQL，并在使用 SQL Developer 时在命令行上显示结果集.创建一个存储函数

Update : Yet, there exists an option, even in DB 10g for dynamic pivoting by using SYS_REFCURSOR, eg. using PL/SQL rather than using SQL only, and show the result set on the command line if you're using SQL Developer. Create a stored function

CREATE OR REPLACE FUNCTION get_person_rs RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_str       VARCHAR2(32767);
BEGIN
  WITH tt AS
  ( 
   SELECT PersonId, 
          ROW_NUMBER() OVER (ORDER BY PersonId) AS rn  
     FROM t 
    GROUP BY PersonId 
  )
  SELECT TO_CHAR(RTRIM(XMLAGG(XMLELEMENT(e, 
           'MAX(CASE WHEN PersonId = '||PersonId||
                   ' THEN myfunc(PersonId,myDate) 
                      END) AS Person'||rn         
           , ',')).EXTRACT('//text()').GETCLOBVAL(), ','))
    INTO v_str         
    FROM tt;  

  v_sql :=
  'SELECT myDate, '|| v_str ||'
     FROM t
    GROUP BY myDate';

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;

其中，ROW_NUMBER() 使用了10g中的解析函数，但是LISTAGG()字符串聚合函数是还没有在 10g 中.所以使用 XMLAGG 代替.这个在函数中生成的 SQL 字符串也与上面的完全相同，例如.在条件聚合逻辑中.

in which, ROW_NUMBER() Analytic function, which is available in 10g, is used, but LISTAGG() string aggregation function is not yet in 10g. So XMLAGG is used instead. This generated SQL string within the function is also exactly same as the above one, eg. in Conditionally Aggregated Logic.

然后运行下面的代码:

VAR rc REFCURSOR
EXEC :rc := get_person_rs;
PRINT rc

从 SQL Developer 的命令行查看当前现有数据动态生成的预期结果集.

from SQL Developer's Command Line in order to see the expected result set dynamically generated by currently existing data.

这篇关于使用两个动态值在 oracle v10.2.0 上进行透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！