17,086
社区成员
发帖
与我相关
我的任务
分享
--建表、插入测试数据
CREATE TABLE t_col_row(
ID INT,
c1 VARCHAR2(10),
c2 VARCHAR2(10),
c3 VARCHAR2(10));
INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31');
INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL);
INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33');
INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34');
INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL);
INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35');
INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL);
COMMIT;
--查询结果
SELECT * FROM t_col_row;
--列转行语句:
SELECT id, 'c1' cn, c1 cv
FROM t_col_row
UNION ALL
SELECT id, 'c2' cn, c2 cv
FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;
PROCEDURE PRO_TEST(IN_TABLE_NAME IN VARCHAR2,
out_cur OUT type_ref_cur
--返回动态游标
) IS
v_sql VARCHAR2(4000);
BEGIN
FOR c IN (SELECT utc.COLUMN_NAME--查询指定表所有列名
FROM user_tab_columns UTC
WHERE table_name = upper('你的表名'))
LOOP
v_sql:=v_sql||'SELECT id, 'c1' cn, '||c.column_name||' cv
FROM t_col_row UNION ALL';
END LOOP;
v_sql:=SUBSTR(v_sql,1,LENGTH(v_sql)-9);--去掉最后的union all
OPEN CURSOR cur FOR v_sql;
END;
SELECT 'A' product,'A1' TYPE,3 sum_a,4 sum_b,2 sum_c,'一号仓库' STORE FROM dual
UNION ALL
SELECT 'A' product,'A2' TYPE,4 sum_a,5 sum_b,2 sum_c,'一号仓库' STORE FROM dual
UNION ALL
SELECT 'B' product,'B1' TYPE,5 sum_a,3 sum_b,2 sum_c,'二号仓库' STORE FROM dual
UNION ALL
SELECT 'B' product,'B2' TYPE,5 sum_a,3 sum_b,2 sum_c,'二号仓库' STORE FROM dual
SELECT 'A' product,'A1' TYPE,3 一号仓库sum_a,5 一号仓库sum_b,2 一号仓库sum_c ,0 二号仓库sum_a,0 二号仓库sum_b,0 二号仓库sum_c FROM dual
UNION ALL
SELECT 'A' product,'A2' TYPE,4 一号仓库sum_a,5 一号仓库sum_b,2 一号仓库sum_c ,0 二号仓库sum_a,0 二号仓库sum_b,0 二号仓库sum_c FROM dual
UNION ALL
SELECT 'B' product,'B1' TYPE,0 一号仓库sum_a,0 一号仓库sum_b,0 一号仓库sum_c ,5 二号仓库sum_a,3 二号仓库sum_b,2 二号仓库sum_c FROM dual
UNION ALL
SELECT 'B' product,'B2' TYPE,0 一号仓库sum_a,0 一号仓库sum_b,0 一号仓库sum_c ,5 二号仓库sum_a,3 二号仓库sum_b,2 二号仓库sum_c FROM dual