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;
相关文章