Oracle SQL中的动态数据透视-过程 [英] Dynamic pivot in oracle sql - Procedure

问题描述

此帖子帖子中提供了一种解决方案. 现在，我想在oracle过程中实现以下语句.

I got a solution to dynamic pivot from this post . Now I want to implement the below statements in an oracle procedure.

clear columns
COLUMN temp_in_statement new_value str_in_statement
SELECT DISTINCT 
LISTAGG('''' || myLetter || ''' AS ' || myLetter,',')
    WITHIN GROUP (ORDER BY myLetter) AS temp_in_statement 
FROM (SELECT DISTINCT myLetter FROM myTable);
SELECT * FROM 
(SELECT myNumber, myLetter, myValue FROM myTable)
PIVOT (Sum(myValue) AS val FOR myLetter IN (&str_in_statement));

谢谢.

推荐答案

如果您有一个表，希望在每次调用Stored Proc时插入数据透视表结果，则可以使用以下方法:

If you have a table you want to insert the pivot results each time you call the Stored Proc, you can use this:

CREATE OR REPLACE PROCEDURE dynamic_pivot
AS
  v_sql LONG       := NULL;
  v_statement LONG := NULL;
BEGIN
  SELECT DISTINCT LISTAGG(''''
    || myLetter
    || ''' AS '
    || myLetter,',') WITHIN GROUP (
  ORDER BY myLetter) AS temp_in_statement
  INTO v_statement
  FROM
    (SELECT DISTINCT myLetter FROM test_data
    );
  v_sql := 'insert into pivot_table    
      select * from (SELECT myNumber, myLetter, myValue FROM test_data    
      )    
      PIVOT    
      (      
      SUM(myValue) AS val FOR myLetter IN (' || v_statement || ')    
      )';
  EXECUTE immediate v_sql;
END;

这篇关于Oracle SQL中的动态数据透视-过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！