地区 省份 市 县 商店名称 商品编号 商品名称 规格 进货数量 库存数量
----------------------------------------------------------------------------------------
华北 内蒙 XX市 精品屋 1 巧克力 10*10 30 10
华北 内蒙 XX市 精品屋 23 旺旺 10*20 10 20
华中 河南 安阳市 园艺加工 1 巧克力 10*10 20 30
华中 河南 安阳市 园艺加工 70 小不点 15 20 1
... ...
-----------------------------------------------------------------------------------------
要实现的结果
-------------------------------------------------------------------------------------------
地区 省份 市 县 商店名称 商品编号 商品名称 规格 进货数量 库存数量 商品编号 商品名称 规格 进货数量 库存数量 商品编号 商品名称 规格 进货数量 库存数量
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
华北 内蒙 XX市 精品屋 1 巧克力 10*10 30 10 23 旺旺 10*20 10 20
华中 河南 安阳市 园艺加工 1 巧克力 10*10 20 30 70 小不点 15 20 1
... ...
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
结果部分中的商品名称、ID、规格等是根据数据库有多少商品 生成多少个的
----------------------------------------------------------------------------------------
华北 内蒙 XX市 精品屋 1 巧克力 10*10 30 10
华北 内蒙 XX市 精品屋 23 旺旺 10*20 10 20
华中 河南 安阳市 园艺加工 1 巧克力 10*10 20 30
华中 河南 安阳市 园艺加工 70 小不点 15 20 1
... ...
-----------------------------------------------------------------------------------------
要实现的结果
-------------------------------------------------------------------------------------------
地区 省份 市 县 商店名称 商品编号 商品名称 规格 进货数量 库存数量 商品编号 商品名称 规格 进货数量 库存数量 商品编号 商品名称 规格 进货数量 库存数量
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
华北 内蒙 XX市 精品屋 1 巧克力 10*10 30 10 23 旺旺 10*20 10 20
华中 河南 安阳市 园艺加工 1 巧克力 10*10 20 30 70 小不点 15 20 1
... ...
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
结果部分中的商品名称、ID、规格等是根据数据库有多少商品 生成多少个的
create table tp_test as (
select '华北' "地区", '内蒙' "省份", 'XX市 ' "市县", '精品屋 ' "商店名称", 1 "商品编号", '巧克力' "商品名称", '10*10' "规格", 30 "进货数量", 10 "库存数量" from dual union
select '华北' "地区", '内蒙' "省份", 'XX市 ' "市县", '精品屋 ' "商店名称", 23 "商品编号", '旺旺 ' "商品名称", '10*20' "规格", 10 "进货数量", 20 "库存数量" from dual union
select '华中' "地区", '河南' "省份", '安阳市' "市县", '园艺加工' "商店名称", 1 "商品编号", '巧克力' "商品名称", '10*10' "规格", 20 "进货数量", 30 "库存数量" from dual union
select '华中' "地区", '河南' "省份", '安阳市' "市县", '园艺加工' "商店名称", 70 "商品编号", '小不点' "商品名称", '15 ' "规格", 20 "进货数量", 1 "库存数量" from dual
)
;
declare
v_sql varchar2(1000);
v_column varchar2(500);
v_drop varchar2(50);
begin
with tb2 as
(select
a.*
,row_number() over(partition by 地区,省份,市县,商店名称 order by 商品编号) row_num
from tp_test a
)
select max(replace(SYS_CONNECT_BY_PATH('max(decode(row_num,'||level||',商品编号)) 商品编号'||level||',max(decode(row_num,'||level||',商品名称)) 商品名称'||level||'
,max(decode(row_num,'||level||',规格)) 规格'||level||',max(decode(row_num,'||level||',进货数量)) 进货数量'||level||',max(decode(row_num,'||level||',库存数量)) 库存数量'||level,'/'),'/',',')) into v_column
from dual
connect by level <= (select max(row_num) from tb2)
;
select
decode(count(1),'1','drop table tp_test01','commit') into v_drop
from user_tables where table_name = upper('tp_test01')
;
execute immediate v_drop;
v_sql := 'create table tp_test01
as
select
地区,省份,市县,商店名称'||v_column||'
from (select a.*, 1 row_num from tp_test a where 1=2)
group by 地区,省份,市县,商店名称
'
;
execute immediate v_sql;
v_sql := 'insert into tp_test01
with tb2 as
(
select
a.*
,row_number() over(partition by 地区,省份,市县,商店名称 order by 商品编号) row_num
from tp_test a
)
select
地区,省份,市县,商店名称'||v_column||'
from tb2
group by 地区,省份,市县,商店名称'
;
execute immediate v_sql;
commit;
end;select * from tp_test01;
比如第二行中的华中地区,它的商品ID是1跟70.
实际上我想要实现的结果是这样的
地区到商店名称为固定列,后面的商品是根据数据库有多少不相同的商品,就增加多少个对应的列
然后每条记录中的商品要对应到其中
比如例子中的华北商品ID是1,23,华中ID为1,70.
那结果应该是 华北 1商品-----1数量,23商品-----23数量
华中 1商品-----1数量,空值---------空值,70商品-------70数量
如果再加个华南有两个商品,ID为23,73,那显示的结果应该这样
华南 空值------空值, 23商品-----23数量,空值----------空值,73商品------73数量
v_tab_sql varchar2(4000);
n1 number;
str varchar2(4000);
begin --动态建表
begin
execute immediate 'drop table TP_TEST_temp';
exception
when others then
null;
end;
select max(count(1))
into n1
from TP_TEST t
group by t.地区, t.省份, t.市县, t.商店名称; v_tab_sql := 'create table TP_TEST_temp
(地区 CHAR(4),省份 CHAR(4),市县 CHAR(6),商店名称 CHAR(8),';
if (n1 > 1) then
for i in 1 .. n1 - 1 loop
v_tab_sql := v_tab_sql || '
商品编号' || i || ' NUMBER,
商品名称' || i || ' CHAR(6),
规格' || i || ' CHAR(5),
进货数量' || i || ' NUMBER,
库存数量' || i || ' NUMBER,';
end loop;
end if;
v_tab_sql := v_tab_sql || '
商品编号 NUMBER,商品名称 CHAR(6),规格 CHAR(5),进货数量 NUMBER,库存数量 NUMBER)';
-- dbms_output.put_line(v_tab_sql);
execute immediate v_tab_sql; --补齐数据
for j in (select t.地区, t.省份, t.市县, t.商店名称, count(1) cnt
from TP_TEST t
group by t.地区, t.省份, t.市县, t.商店名称) loop
for ins in 1 .. n1 - j.cnt loop
insert into TP_TEST
select j.地区,
j.省份,
j.市县,
j.商店名称,
null,
null,
null,
null,
null
from dual;
end loop;
end loop; --插入数据
for x in (select t.地区 || ',' || t.省份 || ',' || t.市县 || ',' || t.商店名称 || ',' ||
wm_concat(t.商品编号 || ',' || t.商品名称 || ',' || t.规格 || ',' ||
t.进货数量 || ',' || t.库存数量) str
from TP_TEST t
group by t.地区, t.省份, t.市县, t.商店名称) loop
str := 'insert into TP_TEST_temp select ';
for i in (select regexp_substr(x.str, '[^,]+', 1, level) str, level lv
from dual
connect by level <= regexp_count(x.str, ',') + 1) loop
str := str || '''' || i.str || ''' c' || i.lv || ',';
end loop;
str := substr(str, 1, length(str) - 1) || ' from dual';
-- dbms_output.put_line(str);
execute immediate str;
str := '';
end loop;
commit;
end;