建表语句
create table SalesList(
keHu varchar2(20), --客户
shangPinId number(8), --商品Id
shangPin varchar2(20), --商品名称
salesNum number(8) --销售数量
);
--插入数据
declare
--谈几个客户
cursor lr_kh is
select regexp_substr('张三、李四、王五、赵六','[^、]+',1, level) keHu from dual
connect by level <= 4;
--进点货
cursor lr_sp is
select level shangPinId, regexp_substr('上衣、裤子、袜子、帽子','[^、]+',1, level) shangPin from dual
connect by level <= 4;
begin
--循环插入
for v_kh in lr_kh loop
for v_sp in lr_sp loop
insert into SalesList
select v_kh.keHu, v_sp.shangPinId, v_sp.shangPin, floor(dbms_random.value(10,50)) from dual;
end loop;
end loop;
commit;
end;
/
执行select * from SalesList; 得到下列结果
KEHU SHANGPINID SHANGPIN SALESNUM
1 张三 1 上衣 33
2 张三 2 裤子 25
3 张三 3 袜子 12
4 张三 4 帽子 49
5 李四 1 上衣 19
6 李四 2 裤子 29
7 李四 3 袜子 24
8 李四 4 帽子 12
9 王五 1 上衣 38
10 王五 2 裤子 35
11 王五 3 袜子 17
12 王五 4 帽子 13
13 赵六 1 上衣 19
14 赵六 2 裤子 21
15 赵六 3 袜子 20
16 赵六 4 帽子 12
--固定行转列
select *
from (select kehu, shangPin, salesNum from salesList) pivot(
max(salesNum) for shangPin in (
'上衣' as 上衣,
'裤子' as 裤子,
'袜子' as 袜子,
'帽子' as 帽子
)
);
--动态行转列
call p_RowsToCols('select keHu, shangPin, salesNum from salesList',
'select distinct shangPinId, shangPin cols from salesList order by shangPinId',
'max(salesNum)',
'shangPin',
'sales_RowsToCols');
--以上两种查询结果均为:
KEHU 上衣 裤子 袜子 帽子
1 王五 38 35 17 13
2 赵六 19 21 20 12
3 李四 19 29 24 12
4 张三 33 25 12 49
--动态行转列执行得是以下存储过程:
create or replace procedure p_RowsToCols(as_sql in varchar2 --源数据的查询sql
,as_sql_cols in varchar2 --动态转换列的查询sql,要求转为列的那列,字段名必须为cols,支持排序
,as_aggCol in varchar2 --对应pivot函数的 聚合函数
,as_changeCol in varchar2 --源数据中,要转为列的字段名
,as_viewName in varchar2 --结果输出的视图名,执行完后查此视图即可
) is
ls_sql varchar2(4000);
ls_in varchar2(4000);
begin
--拼接in的内容
ls_sql := 'select listagg(''''''''||cols||'''''' "''||cols||''"'', '','')within group(order by rn) ' ||
'from (select rownum rn, cols from (' || as_sql_cols || '))';
execute immediate ls_sql
into ls_in;
--创建视图
ls_sql := 'create or replace view ' || as_viewName ||' as ' ||
'select * from (' || as_sql || ') ' ||
'pivot (' || as_aggCol || ' for ' || as_changeCol || ' in (' || ls_in || '))';
execute immediate ls_sql;
end p_RowsToCols;
网友评论