本文介绍了oracle sql中的动态数据透视 - 过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我从这个 post 中得到了动态数据透视的解决方案.现在我想在 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时插入pivot结果，你可以使用这个:
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中的动态数据透视 - 过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！