原表 item_shop
booking(订单号) item(货物)
1 面包
2 水蜜桃
2 啤酒
3 香蕉
4 盐汽水
要得到如下结果:
booking 面包 水蜜桃 啤酒 香蕉 盐汽水
1 1 0 0 0 0
2 0 1 1 0 0
3 0 0 0 1 0
4 0 0 0 0 1注意:有货物销售记录的订单号为1,无销售记录的订单号为2,货物是不固定的,有上千种之多~求教下,有什么办法得到结果吗?
booking(订单号) item(货物)
1 面包
2 水蜜桃
2 啤酒
3 香蕉
4 盐汽水
要得到如下结果:
booking 面包 水蜜桃 啤酒 香蕉 盐汽水
1 1 0 0 0 0
2 0 1 1 0 0
3 0 0 0 1 0
4 0 0 0 0 1注意:有货物销售记录的订单号为1,无销售记录的订单号为2,货物是不固定的,有上千种之多~求教下,有什么办法得到结果吗?
不明白是什么意思参考
http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.htmlbegin
row_to_col('item_shop','booking','item','1','count');
end;select * from v_tmp;但如果货物有上千种的话,拼出的语句将超出4000字节,不能使用上述的方法。
试试将拼语句的过程写在程序端,拼好后传入ORACLE执行,也许能突破这个限制?
least(count(decode(...)),1)
create or replace procedure row_col(
参数
) is
sqlstr varchar2(4000);
r1 mycursor%rowtype;
cursor mycursor is select ', max(decode(item,'||chr(39)||item||chr(39)||',1,0))'||'"'||item||'" ' c2 from item_shop group by item;
begin
sqlstr :='select 需要返回的列';
open mycursor;
loop
fetch mycursor into r1;
exit when mycursor%notfound;
sqlstr:=sqlstr || r1.c2;
end loop;
close mycursor;
sqlstr:=sqlstr||' from item group by item'; open v_cirsor for sqlstr;
end row_col;你说的情况大概就这个样子,改改就好了
r1 mycursor%rowtype;
cursor mycursor is select ', max(decode(item,'||chr(39)||item||chr(39)||',1,0))'||'"'||item||'" ' c2 from item_shop group by item;
应该是
cursor mycursor is select ', max(decode(item,'||chr(39)||item||chr(39)||',1,0))'||'"'||item||'" ' c2 from item_shop group by item;
r1 mycursor%rowtype;
1、首先找出当天的货物数
select distinct item from table_name
2、根据上面的货物数生成一张表,动态添加列
create table book_count(booking int)
declare cursor item_cursor is
select distinct item from item_shop;
begin
for item_row in item_cursor
loop
execute immediate('alter table book_count add ' || item_row || ' int);
end loop
end
3、最后更新列的值
declare cursor item_curosr is
select * from item_shopbegin
for item_id in booking
loop
update book_count....
end loop
end
SQL> select * from item_shop t; BOOKING ITEM
---------- --------------------
1 面包
2 水蜜桃
2 啤酒
3 香蕉
4 盐汽水
3 西瓜
3 冬瓜已选择7行。
--新建一个表存储结果
create table item_shop_tmp(booking number);
--扩展表结构
begin
for cur in (select item from item_shop group by item) loop
execute immediate('alter table item_shop_tmp add ' || cur.item || ' varchar2(100)');
end loop;
end;
--查看结果
select * from item_shop_tmp t;
--插入booking
insert into item_shop_tmp(booking ) select booking from item_shop group by booking ;
--得到最后的结果
begin
for cur in (select column_name
from user_tab_columns
where upper(table_name) = upper('item_shop_tmp')
and column_name <> 'BOOKING') loop
execute immediate 'update item_shop_tmp t set ' || cur.column_name ||
'=(select decode(count(*),0,0,1) from item_shop where booking=t.booking and item =''' ||
cur.column_name || ''') ';
end loop;
end;
上面的步骤也可以放到一个过程中执行。
execute immediate 'update item_shop_tmp t set ' || cur.column_name ||
'=(select decode(count(*),0,0,1) from item_shop where booking=t.booking and item =''' ||
cur.column_name || ''') ';
这句可改成如下形式比较下
execute immediate 'update item_shop_tmp t set ' || cur.column_name ||
'=(select count(booking) from item_shop where booking=t.booking and item =''' ||
cur.column_name || ''' and rownum<2) ';
对item_shop表的booking和item字段建联合索引
执行这段
--扩展表结构
begin
for cur in (select item from item_shop group by item) loop
execute immediate('alter table item_shop_tmp add ' || cur.item || ' varchar2(100)');
end loop;
end;
报 ORA-01735 无效的ALTER TABLE 选项。