oracle中要实现行转列的方式有很多种,比如case when …else …end 、wm_concat()函数,lag() over() 、lead() over() 函数等,以及11g版本后的pivot函数都可实现。可根据具体的需求选取不同的方式。
前两天恰好一朋友问起如何将如下表1动态转成表2的形式。
表1
表2
,channel_flag是查数据字典得到,值是变动的。当时第一反应是使用pivot函数。pivot函数语法是pivot(聚合函数 for 列名 in(类型))。尝试了几次,in后接子查询均报错,查资料得知若后接子查询,只能使用pivot xml函数,显然xml格式不符合需求。故最终选择使用存储过程来实现。最终的实现过程如下:
CREATE OR REPLACE PROCEDURE p_tmp20190730 AUTHID CURRENT_USER IS
V_SQL VARCHAR2(2000);
CURSOR CURSOR_1 IS SELECT DISTINCT channel_flag FROM tmp20190730 ORDER BY channel_flag;
BEGIN
V_SQL := 'SELECT prod_code';
FOR v_channel_flag IN CURSOR_1
LOOP
V_SQL := V_SQL || ',' || 'MAX(DECODE(channel_flag,''' || v_channel_flag.channel_flag ||
''',isallow,null)) AS ' || '"' || v_channel_flag.channel_flag || '"' ;
END LOOP;
V_SQL := V_SQL || ' FROM tmp20190730 GROUP BY prod_code';
DBMS_OUTPUT.PUT_LINE(V_SQL);
V_SQL := 'CREATE TABLE TMP_RESULT AS '|| V_SQL;
EXECUTE IMMEDIATE V_SQL;
END;
tmp20190730为表1,最终结果tmp_result为表2.过程加
AUTHID CURRENT_USER防止出现执行存储过程权限不足。