动态行转列 sql怎么写
动态行转列 sql怎么写
表 table
id prodid lieming value dingdan
1 p01 数量 50 ding1
2 p01 价格 10 ding1
3 p01 赠送数量 1 ding1
4 p02 数量 50 ding1
5 p02 价格 10 ding1
6 p02 赠送数量 0 ding1
类似这样的， 不过lieming是不固定的 可以动态添加的，可以添加5列 10列 20列 所以 数量、价格这些是不能写死的，最后dingdan查询出来， 具体的这个sql应该怎么写？ 万分感谢了
[解决办法]
给你个找来的例子，你也可以百度搜索的
需求：
昨天客户有个新的需求，就是根据原始表的数据转行后在报表中显示。其实就是一个行列转换
具体来说，这里简化一些其它需求，只是讲述行列转换的具体内容：
客户有张原始的表(假设temp1)，表中只有4个栏位(假设id,name,sex,provience)，但是表中数据量不确定，随时会改变。
现在客户要每跑一次批次就把temp1表的信息转换到另一张表(假设temp2)，需要注意的是，temp2表的栏位跟temp1的数据是对应的
举个例子来说，
假设 temp1表数据有4条：
ID NAME SEX PROVENCE
---------- ----- ---- ----------
XQ07020001 张三 男 成都
XQ07020002 李四 男 眉山
XQ07020003 王五 女 乐山
XQ07020004 刘倩 男 南充
而现在客户由于报表上要显示temp1转换后的temp2中的数据，批次中固定只会最多抓取temp1表的10条数据
一个一个的写入temp2与之对应的栏位。也就是说现在temp2表结构式固定的，
如下：(其中最后两列是异动日期txdat和移动时间txtm，不管temp1中数据有多少，这两个栏位都是存在的！)
COL1 COL2 COL3 COL4 COL5 ... COL10 TXDAT TXTM
-------------------------------------------
XQ07020001 XQ07020002 XQ07020003 XQ07020004 20101228 13125000
张三 李四 王五 刘倩 20101228 13125000
男 男 女 男 20101228 13125000
成都 眉山 乐山 南充 20101228 13125000
当源数据temp1中不足10笔数据的时候，temp2后面小于10的栏位就是空值，如上数据。
这里需要补充一点最关键的是，栏位的类型和长度，COL1，COL2，COL3.。。。都是varchar2(20)，
而原表temp1中各个栏位都是varchar2，长度也不超过10，这就对后面的实现操作就简单多了。
测试SQL脚本：
DROP TABLE temp2;
CREATE TABLE temp1 (id VARCHAR2(10),name VARCHAR2(10),sex VARCHAR2(6),provence VARCHAR2(20) );
CREATE TABLE temp2 (col1 VARCHAR2(20),col2 VARCHAR2(20),col3 VARCHAR2(20),col4 VARCHAR2(20),col5 VARCHAR2(20)
,col6 VARCHAR2(20),col7 VARCHAR2(20),col8 VARCHAR2(20),col9 VARCHAR2(20),col10 VARCHAR2(20)
,txdat VARCHAR2(8),txtm VARCHAR2(8)) ;
INSERT INTO temp1 VALUES('XQ07020001','张三','男','成都');
INSERT INTO temp1 VALUES('XQ07020002','李四','男','眉山');
INSERT INTO temp1 VALUES('XQ07020003','王五','女','乐山');
INSERT INTO temp1 VALUES('XQ07020004','刘倩','男','南充');
INSERT INTO temp1 VALUES('XQ07020005','潘金莲','女','泸州');
INSERT INTO temp1 VALUES('XQ07020006','西门庆','男','宜宾');
INSERT INTO temp1 VALUES('XQ07020007','赵六','女','雅安');
INSERT INTO temp1 VALUES('XQ07020008','刘德华','男','攀枝花');
INSERT INTO temp1 VALUES('XQ07020009','黎明','男','宜宾');
INSERT INTO temp1 VALUES('XQ07020010','春春','女','雅安');
INSERT INTO temp1 VALUES('XQ07020011','小丽','男','攀枝花');
。。。。
。。。。
需求基本如上，简化了其它逻辑，只考虑行列转换这个逻辑。后面我还会讨论temp2表结构不固定的情况。
由于这个需求应该说是固定的行列转换，单纯的group+Max 就能实现。但是我这里采用的二维数组来实现，批次调用过程来做。
--过程：
CREATE OR REPLACE PROCEDURE two_table_arrary_test
AS
--定义二维数组:
TYPE one_table_type IS TABLE OF VARCHAR(200) INDEX BY BINARY_INTEGER;
TYPE two_table_type IS TABLE OF one_table_type INDEX BY BINARY_INTEGER;
rec_arr two_table_type;
--定义游标,获取来源资料集
CURSOR cur_temp1 IS SELECT id,name,sex,provence FROM temp1 ORDER BY id;
rec_temp1 cur_temp1%ROWTYPE;
wrk_txdat VARCHAR2(8); --异动日期
wrk_txtm VARCHAR2(8); --异动时间
temp_cnt NUMBER :=0; --数组二维下标
BEGIN
--获取异动日期和时间
SELECT TO_CHAR(sysdate,'YYYY')
[解决办法]
TO_CHAR(sysdate,'MM')
[解决办法]
TO_CHAR(sysdate,'DD') into wrk_txdat FROM dual;
SELECT TO_CHAR(sysdate,'hh24miss')
[解决办法]
'00' into wrk_txtm FROM dual;
--每次清掉temp2中数据
DELETE FROM temp2;
OPEN cur_temp1;
LOOP
FETCH cur_temp1 INTO rec_temp1;
EXIT WHEN cur_temp1%NOTFOUND;
--初始化数组
rec_arr(0)(temp_cnt) := rec_temp1.id;
rec_arr(1)(temp_cnt) := rec_temp1.name;
rec_arr(2)(temp_cnt) := rec_temp1.sex;
rec_arr(3)(temp_cnt) := rec_temp1.provence;
temp_cnt := temp_cnt + 1;
END LOOP;
CLOSE cur_temp1;
--每次只抓取10笔资料，不足10笔补空
IF temp_cnt < 10 THEN
FOR i IN temp_cnt..9 loop
rec_arr(0)(temp_cnt) := '' ;
rec_arr(1)(temp_cnt) := '' ;
rec_arr(2)(temp_cnt) := '' ;
rec_arr(3)(temp_cnt) := '' ;
temp_cnt := temp_cnt + 1 ;
END LOOP;
END IF;
--往temp2插入数据
FOR i IN 0..3 LOOP
INSERT INTO temp2 (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,txdat,txtm)
VALUES (rec_arr(i)(0),rec_arr(i)(1),rec_arr(i)(2),rec_arr(i)(3),rec_arr(i)(4)
,rec_arr(i)(5),rec_arr(i)(6),rec_arr(i)(7),rec_arr(i)(8),rec_arr(i)(9)
,wrk_txdat,wrk_txtm);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20102,'** two_table_arrary_test error **'
[解决办法]
sqlerrm);
END two_table_arrary_test;
/
客户需求已经实现，由于temp2表结构是固定的，很单纯，因为对于客户来说，报表中显示的栏位肯定是固定的，
也就是temp2为什么每次只会抓取temp1中10笔资料。
下面来讨论假设temp2表结构不固定的情况。也就是说temp1中数据有几笔，temp2中col就有几个，对应col1,col2,col3....
--如下，动态过程
CREATE OR REPLACE PROCEDURE two_table_arrary_test2
AS
--定义二维数组:
TYPE one_table_type IS TABLE OF VARCHAR(200) INDEX BY BINARY_INTEGER;
TYPE two_table_type IS TABLE OF one_table_type INDEX BY BINARY_INTEGER;
rec_arr two_table_type;
--定义游标,获取来源资料集
CURSOR cur_temp1 IS SELECT id,name,sex,provence FROM temp1 ORDER BY id;
rec_temp1 cur_temp1%ROWTYPE;
wrk_txdat VARCHAR2(8); --异动日期
wrk_txtm VARCHAR2(8); --异动时间
temp_cnt NUMBER :=0; --数组二维下标
cnt NUMBER :=0; --判断是否temp3存在
create_sql VARCHAR2(4000); --动态create temp3的SQL
insert_sql VARCHAR2(4000); --动态insert temp3的SQL
BEGIN
--获取异动日期和时间
SELECT TO_CHAR(sysdate,'YYYY')
[解决办法]
TO_CHAR(sysdate,'MM')
[解决办法]
TO_CHAR(sysdate,'DD') into wrk_txdat FROM dual;
SELECT TO_CHAR(sysdate,'hh24miss')
[解决办法]
'00' into wrk_txtm FROM dual;
--判断表temp3是否存在，存在就drop
SELECT Count(*) INTO cnt FROM user_tables WHERE table_name='TEMP2';
IF cnt>0 THEN
EXECUTE IMMEDIATE 'drop table temp2';
END IF;
OPEN cur_temp1;
LOOP
FETCH cur_temp1 INTO rec_temp1;
EXIT WHEN cur_temp1%NOTFOUND;
--初始化数组
rec_arr(0)(temp_cnt) := rec_temp1.id;
rec_arr(1)(temp_cnt) := rec_temp1.name;
rec_arr(2)(temp_cnt) := rec_temp1.sex;
rec_arr(3)(temp_cnt) := rec_temp1.provence;
temp_cnt := temp_cnt + 1;
END LOOP;
CLOSE cur_temp1;
/*
--每次只抓取10笔资料
IF temp_cnt < 10 THEN
FOR i IN temp_cnt..9 loop
rec_arr(0)(temp_cnt) := '' ;
rec_arr(1)(temp_cnt) := '' ;
rec_arr(2)(temp_cnt) := '' ;
rec_arr(3)(temp_cnt) := '' ;
temp_cnt := temp_cnt + 1 ;
END LOOP;
END IF;
*/
--动态创建表temp3
create_sql:='create table temp2 (';
FOR i IN 1..temp_cnt LOOP
create_sql:=create_sql
[解决办法]
'col'
[解决办法]
i
[解决办法]
' varchar2(20),';
END LOOP;
create_sql:=create_sql
[解决办法]
'txdat varchar2(8),txtm varchar2(8))';
EXECUTE IMMEDIATE create_sql;
--动态往temp3插入数据
FOR i IN 0..3 LOOP
insert_sql:='insert into temp2 (';
FOR j IN 1..temp_cnt LOOP
insert_sql:=insert_sql
[解决办法]
'col'
[解决办法]
j
[解决办法]
',';
END LOOP;
insert_sql:=insert_sql
[解决办法]
'txdat,txtm) values(''';
FOR j IN 0..temp_cnt-1 LOOP
insert_sql:=insert_sql
[解决办法]
rec_arr(i)(j)
[解决办法]
''',''';
END LOOP;
insert_sql:=insert_sql
[解决办法]
wrk_txdat
[解决办法]
''','''
[解决办法]
wrk_txtm
[解决办法]
''')';
EXECUTE IMMEDIATE insert_sql;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20102,'** two_table_arrary_test2 error **'
[解决办法]
sqlerrm);
END two_table_arrary_test2;