多字段的行转列查询？

Question

给你看看这个：
[Q]如何实现行列转换
[A]1、固定列数的行列转换
如
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为 
语文 数学 英语
student1 80 70 60
student2 90 80 100
……
语句如下：
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student

Answer 1

这样只查询出一个字段的记录，
select 
sum(decode(substr(month)),'01',num1,0))"1月",
sum(decode(substr(month)),'02',num1,0))"2月",
sum(decode(substr(month)),'03',num1,0))"3月",
sum(decode(substr(month)),'04',num1,0))"4月",　
sum(decode(substr(month)),'05',num1,0))"5月",　
sum(decode(substr(month)),'06',num1,0))"6月",
sum(decode(substr(month)),'07',num1,0))"7月",
sum(decode(substr(month)),'08',num1,0))"8月",　
sum(decode(substr(month)),'09',num1,0))"9月",
sum(decode(substr(month)),'10',num1,0))"10月",
sum(decode(substr(month)),'11',num1,0))"11月",
sum(decode(substr(month)),'12',num1,0)) "12月"
from biao
group by substr(month,1,4to_char(month,'yyyy-mm-dd')
字段有50个？？
如果用数组，类型也很难定义。

Answer 2

是的，都是数值型的，如果字段很多，SQL语句怎么写，里面的num1能不能用一个变量来代替?

Answer 3

我觉得这么写起来很麻烦，字段多要做循环，再写动态sql。
你有没有试过二维数组，把表里数据取出来放在数组里，然后再把数组的两个下标交换就是新表的结构了。
看看那种做法效率高

Answer 4

原先也是想用数组的，但不会定义，帮我看一下，这样定义行不行。
   --单维数组
   TYPE vtmp IS TABLE OF NUMBER
   INDEX BY BINARY_INTEGER;   --记录集
   TYPE tmp IS RECORD
   ( num1 number,num2 number,num3 number,
     num4 number,num5 number,num6 number,
     num7 number,num8 number,num9 number,
     num10 number,num11 number,num12 number);查出原表的记录，放入记录集中，然后再赋给 单维数组

Answer 5

下面有个例子，你看看吧
---------------------- 单维数组 ------------------------DECLARE
TYPE emp_ssn_array IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;best_employees emp_ssn_array;
worst_employees emp_ssn_array;BEGIN
best_employees(1) := '123456';
best_employees(2) := '888888';worst_employees(1) := '222222';
worst_employees(2) := '666666';FOR i IN 1..best_employees.count LOOP
DBMS_OUTPUT.PUT_LINE('i='|| i || ', best_employees= ' ||best_employees(i)
|| ', worst_employees= ' ||worst_employees(i));
END LOOP;END;---------------------- 多维数组 ------------------------ 
DECLARETYPE emp_type IS RECORD
( emp_id employee_table.emp_id%TYPE,
emp_name employee_table.emp_name%TYPE,
emp_gender employee_table.emp_gender%TYPE );TYPE emp_type_array IS TABLE OF
emp_type INDEX BY BINARY_INTEGER;emp_rec_array emp_type_array;
emp_rec emp_type;BEGIN
emp_rec.emp_id := 300000000;
emp_rec.emp_name := 'Barbara';
emp_rec.emp_gender := 'Female';emp_rec_array(1) := emp_rec;emp_rec.emp_id := 300000008;
emp_rec.emp_name := 'Rick';
emp_rec.emp_gender := 'Male';emp_rec_array(2) := emp_rec;FOR i IN 1..emp_rec_array.count LOOP
DBMS_OUTPUT.PUT_LINE('i='||i
||', emp_id ='||emp_rec_array(i).emp_id
||', emp_name ='||emp_rec_array(i).emp_name
||', emp_gender = '||emp_rec_array(i).emp_gender);
END LOOP; END;
-------------- Result --------------
i=1, emp_id =300000000, emp_name =Barbara, emp_gender = Female
i=2, emp_id =300000008, emp_name =Rick, emp_gender = Male

Answer 6

如果把原表的查询记录存入 RECORD 记录集中，等下循环插入新表的语句就不好写了，总不能把50个字段对应的插入语句一个个写出来吧。

Answer 7

TO waterfirer(水清):帮我想想有没有什么好的办法，谢谢，如果能用循环语句插入数据，也可以。

Answer 8

我觉得是两种办法
1、做循环，每次从表中取出一列记录（50个字段就是五十次），再做循环（12个月就是12次）将数据拼成 insert into tablename values(值1,值2,...,值12)的样子，然后执行。出错一块回滚。
这样做会50次连接原表，但无需定义数组。
2、定义数组，一次从原表取出记录，存在数组里（这里也要做循环加，12个月就是12次）。然后做两层循环将插入语句拼好，执行（和第一种拼串方式一样）。出错一块回滚。
这样做只一次连接原表，但定义数组占用空间，给数组赋值也会占用时间。两种做法究竟哪种合适试试才知道。

Answer 9

还是想用第二种方法，因为如果原表不止是一个表，而是要从多个表中取数据，就只能用第二种方法。如果插入语句是：
INSERT INTO biao1 VALUES('aa',5,'bb','吨',varry(1).num1,varry(2).num1,varry(3).num1,varry(4).num1,varry(5).num1,varry(6).num1,varry(7).num1,varry(8).num1,varry(9).num1,varry(10).num1,varry(11).num1,varry(12).num1,varry(13).num1,varry(14).num1,varry(15).num1,varry(16).num1,varry(17).num1,varry(18).num1,varry(19).num1,varry(20).num1,varry(21).num1,varry(22).num1,varry(23).num1)用以下动态语句为什么老出错：
vkind:='aa';
vseq:=5;
vname:='bb';
vunit:='吨';FOR i IN 1..23 LOOP
    vstr:=vstr||'varry('||to_char(i)||').num1,';
END LOOP;
    vstr:=substr(vstr,1,length(vstr)-1)||')';    str_sql:='INSERT INTO biao1 VALUES(:1,:2,:3,:4,'||vstr;
            
    execute immediate str_sql USING vkind,vseq,vname,vunit;   再帮忙看一下。拜托。

Answer 10

执行上面的语句，出现下面错误信息：
ORA-00904: "VARRY": 无效的标识符难道动态语句中不能用到括号？

Answer 11

vstr:=vstr||'varry('||to_char(i)||').num1,';
此处应该是
vstr:=vstr|| varry(to_char(i)).num1 || ',';

Answer 12

如果num1中1也要写成动态的，怎么写啊？vstr:=vstr|| varry(to_char(i)).num?? || ',';

Answer 13

直接把numj定义成j，然后写成varry(to_char(i)).j看看行不行

Answer 14

怎么定义，类型要定义成什么。
FOR a IN 1..10 Loop
  vtmp:='num'||to_char(a);
  vstr:=vstr|| varry(to_char(i)).vtmp|| ',';
END LOOP定义成字符型，出错啊。

Answer 15

如果RECORD记录集中的项名称不能用动态的来写，那就不能进行第二层循环了。
   TYPE tmp IS RECORD
   ( num1 number(18,4),num2 number(18,4),num3 number(18,4);

Answer 16

我这里只能上MSN,账号是：[email protected]

Answer 17

▲多维数组(表)
声明PL/SQL表的语法如下：
TYPE PL/SQL表名 IS TABLE OF 数据类型 INDEX BY BINARY_INTEGER;
/*下面的例子变换一个表的行与列*/
DECLARE
TYPE phoneNum IS RECORD (
phone_no VARCHAR(13) NOT NULL DEFAULT '----',
city_code VARCHAR(4) NOT NULL DEFAULT '----',
city_name VARCHAR(8) NOT NULL DEFAULT '----',
state VARCHAR(6) NOT NULL DEFAULT '----',
type VARCHAR(6) NOT NULL DEFAULT '----'
); /*声明记录类型数据*/
TYPE emp_type_array IS TABLE OF phoneNum INDEX BY BINARY_INTEGER;/*声明多维数组*/
selPart emp_type_array; /*声明多维数组变量*/
loopCount INTEGER;
colTotal INTEGER:=5; /*总计列数*/ CURSOR curSel IS /*声明游标*/
SELECT phone_no,city_code,city_name,state,type 
FROM tb_phone_no
WHERE phone_no in ('1333241','1339414','1334237','1333222','1332230');BEGIN
loopCount:=0;
OPEN curSel; /*打开游标*/
FETCH curSel INTO selPart(loopCount); /*从游标中检索数据行*/
WHILE curSel%FOUND LOOP /*当不再能找到数据时，终止查询*/
loopCount:=loopCount+1;
FETCH curSel INTO selPart(loopCount);
END LOOP;
CLOSE curSel; /*关闭游标*/ DBMS_OUTPUT.PUT_LINE('正常显示：');
FOR loopCount IN 0..selPart.count-1 LOOP
DBMS_OUTPUT.PUT_LINE(selPart(loopCount).phone_no ||
' ' || selPart(loopCount).city_code ||
' ' || selPart(loopCount).city_name ||
' ' || selPart(loopCount).state ||
' ' || selPart(loopCount).type);
END LOOP;
DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('行列转换：'); FOR loopCount IN 0..colTotal-1 LOOP
DBMS_OUTPUT.PUT(selPart(loopCount).phone_no || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
FOR loopCount IN 0..colTotal-1 LOOP
DBMS_OUTPUT.PUT(selPart(loopCount).city_code || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
FOR loopCount IN 0..colTotal-1 LOOP
DBMS_OUTPUT.PUT(selPart(loopCount).city_name || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
FOR loopCount IN 0..colTotal-1 LOOP
DBMS_OUTPUT.PUT(selPart(loopCount).state || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
FOR loopCount IN 0..colTotal-1 LOOP
DBMS_OUTPUT.PUT(selPart(loopCount).type || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END;
/*运行示例*/
SQL> @d:\temp\oracle.sql
 61  /
正常显示：
1332230 0417    营口    辽宁    CDMA
1333222 0411    大连    辽宁    CDMA
1334237 0427    盘锦    辽宁    CDMA
1339414 0414    本溪    辽宁    CDMA
1333241 024     沈阳    辽宁    CDMA
------------------------------------------------
行列转换：
1332230 1333222 1334237 1339414 1333241 
0417    0411    0427    0414    024     
营口    大连    盘锦    本溪    沈阳    
辽宁    辽宁    辽宁    辽宁    辽宁    
CDMA    CDMA    CDMA    CDMA    CDMA    PL/SQL 过程已成功完成。

Answer 18

如果记录集这样定义：
    --记录集(存储单条查询结果)
   TYPE tmp111 IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;
   TYPE tmp2 IS RECORD ( zhi tmp111 );
   vtab tmp2;
   
    --单维数组
   TYPE arr2 IS TABLE OF tmp2 INDEX BY BINARY_INTEGER;
   vary arr2;那么第二层就可以用循环了：
 如： vary(i).zhi(i)

Answer 19

谢谢大家的帮助，特别是waterfirer(水清)