oracle 动态sql列转行_Oracle 行转列 动态出转换的列
10月的第二天，前天寫了個Oracle中行轉(zhuǎn)列的pivot的基本使用方法，然后，因為pivot的用法中，正常情況下，我們需要轉(zhuǎn)出多少個列，都得在我們的sql中完完整整地寫出，而不能直接在里面寫個查詢來動態(tài)轉(zhuǎn)換。然后，趁著祖國母親的生日，這幾天放假，整理一下處理方法。
一、運行環(huán)境
Win10，Oracle Database 11g r2，plsql 12。
二、效果預(yù)覽
1、固定轉(zhuǎn)換列的方法
2、存儲過程處理
1)調(diào)用存儲過程
2)查指定的視圖即可
3、兩種方法的關(guān)系
其實原理很簡單，就是通過動態(tài)sql，去把你不愿意寫，或者說是不確定的轉(zhuǎn)換列數(shù)，通過查詢查出來，拼接進去，然后執(zhí)行拼接后的sql，創(chuàng)建視圖。
三、存儲過程
create or replace procedure p_RowsToCols(as_sql in varchar2 --源數(shù)據(jù)的查詢sql
,as_sql_cols in varchar2 --動態(tài)轉(zhuǎn)換列的查詢sql,要求轉(zhuǎn)為列的那列，字段名必須為cols，支持排序
,as_aggCol in varchar2 --對應(yīng)pivot函數(shù)的 聚合函數(shù)
,as_changeCol in varchar2 --源數(shù)據(jù)中，要轉(zhuǎn)為列的字段名
,as_viewName in varchar2 --結(jié)果輸出的視圖名，執(zhí)行完后查此視圖即可
) is
ls_sql varchar2(4000);
ls_in varchar2(4000);
begin
--拼接in的內(nèi)容
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;
--創(chuàng)建視圖
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;
四、測試數(shù)據(jù)及SQL
貼一下我測試的數(shù)據(jù)和代碼。
--建表
--drop table SalesList;
create table SalesList(
keHu varchar2(20), --客戶
shangPinId number(8), --商品Id
shangPin varchar2(20), --商品名稱
salesNum number(8) --銷售數(shù)量
);
--插入數(shù)據(jù)
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
--循環(huán)插入
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;
/
--看下源數(shù)據(jù)
select * from salesList a;
--固定行轉(zhuǎn)列
select *
from (select kehu, shangPin, salesNum from salesList) pivot(
max(salesNum) for shangPin in (
'上衣' as 上衣,
'褲子' as 褲子,
'襪子' as 襪子,
'帽子' as 帽子
)
);
--動態(tài)行轉(zhuǎn)列
call p_RowsToCols('select keHu, shangPin, salesNum from salesList',
'select distinct shangPinId, shangPin cols from salesList order by shangPinId',
'max(salesNum)',
'shangPin',
'sales_RowsToCols');
select * from sales_RowsToCols;
完結(jié)！！！！！！！！！！！！！！！！！！！！！！
結(jié)尾來個悲傷的彩蛋，聞?wù)邆?#xff0c;聽者落淚！
上面介紹的方法是一個很簡單的思路，然鵝！！！在找到這個思路之前，我還傻傻的做了另外一個版本，一個比較復(fù)雜的版本。。。花了有差不多半天時間。我按照這個思路，剛做完的時候有多開心，現(xiàn)在的我，就有多傷心！！不過不忍心直接del掉，還是在這邊記錄一下吧，思路大概是這個樣幾：
同樣是存儲過程，傳入數(shù)據(jù)源的查詢sql，通過存儲過程處理，拼接成完整的pivot函數(shù)需要的sql，然后直接執(zhí)行，查出結(jié)果，insert到一張通用的記錄表中，然后建一個視圖，指向這部分?jǐn)?shù)據(jù)。
完結(jié)篇.1?效果預(yù)覽
測試數(shù)據(jù)參考上面的插入sql，同樣的數(shù)據(jù)。
1、第一個參數(shù)：必填，數(shù)據(jù)源的查詢，列數(shù)不限，但查詢的倒數(shù)第二列為轉(zhuǎn)換列，最后一列數(shù)據(jù)列；
2、第二個參數(shù)：
1)可為空，內(nèi)容為要轉(zhuǎn)換出的列的查詢sql，可指定表頭順序及顯示的表頭名稱。為空時即從數(shù)據(jù)源查詢的倒數(shù)第二列中取distinct值，并不保證排序。
2)查詢結(jié)果必須為三列，第一列為數(shù)字排序列(不需要的話這列就隨便指定一個數(shù)字就行)，第二列對應(yīng)數(shù)據(jù)源中轉(zhuǎn)換列的值，第三列即為對應(yīng)的表頭轉(zhuǎn)換后的名稱(如果名稱不需要改變，這列跟第二列保持一樣就行)。
3、第三個參數(shù)：可為空，內(nèi)容就是處理完后要查的視圖名。為空的話，即默認為tmp_rowToCol。
完結(jié)篇.2?跟pivot的對應(yīng)關(guān)系
完結(jié)篇.3?其他好玩的東西
1、因為我們這個方法的話是吧查出的內(nèi)容存到表里，然后通過視圖直接指向數(shù)據(jù)的，剛才指定的視圖Sale_RowToCol，具體創(chuàng)建語法，就是下面這個，tmp_RowToCol_XiaoXianNv為數(shù)據(jù)存儲的表，fbs = '1'?表示是轉(zhuǎn)換的實際數(shù)據(jù)，fguid是本次轉(zhuǎn)換的一個key。
我們查下這個表中實際的數(shù)據(jù)，這是一個在首次調(diào)用存儲過程的時候會創(chuàng)建的一個表，203個字段，不超過這么多列的都可以通過我們的存儲過程去轉(zhuǎn)并存儲數(shù)據(jù)。
然后，這個表的數(shù)據(jù)是會一直保存著的，emm...好像沒啥卵用，不過說不定哪天腦子抽了向往前查查這個還是挺有意思的。。。(說得我自己都不信)
完結(jié)篇.4?貼代碼
csdn的高亮。。好像不太友好
/**
* 動態(tài)進行行列轉(zhuǎn)換，結(jié)果集可在一個可指定的視圖中查詢(默認為tmp_rowToCol)
* 適用于把一列的值轉(zhuǎn)成多列，轉(zhuǎn)換效果與Oracle的pivot相同，但不需要寫死轉(zhuǎn)換出來的每一個列
* 轉(zhuǎn)換后的數(shù)據(jù)所存的實體表為tmp_RowToCol_XiaoXianNv，通過一個guid關(guān)聯(lián)到指定的視圖
* tmp_RowToCol_XiaoXianNv表在此過程中不做刪除操作。所以如果永久了怕是數(shù)據(jù)也會挺多。
* 如果不需要保留數(shù)據(jù)的話，可以考慮把這個表建為一個會話級臨時表，然后轉(zhuǎn)換結(jié)果插入后不提交。
* 這樣在同一會話下可查詢，提交或者回滾后數(shù)據(jù)就不復(fù)存在。
*
* 轉(zhuǎn)換思路：
* 1、通過動態(tài)sql，拼接出 for XXX in () 里面那部分內(nèi)容，然后通過動態(tài)sql執(zhí)行并把結(jié)果插入一個表中
* 2、獲取固定列、轉(zhuǎn)出列的列名，進行拼接，然后創(chuàng)建視圖指向上一步插入的數(shù)據(jù)
*
* author: lhy
* date: 2018-10-01 祖國萬歲
*
* as_sql 要轉(zhuǎn)換的數(shù)據(jù)源查詢
* 對查詢結(jié)果集的要求：至少3列，
* 最后一列為數(shù)據(jù)值
* 倒數(shù)第二列為要轉(zhuǎn)成列的內(nèi)容
* 前面的就是不需轉(zhuǎn)換的列
* as_sql_col 查詢要轉(zhuǎn)的列名，如果不指定，即從as_sql的查詢的倒數(shù)第二列中獲取distinct值
* 對查詢的結(jié)果集要求：必須為三列
* 對應(yīng)pivot函數(shù)中的：for xxx in('值1' as colNm1，'值2' as colNm2 ...)
* 第一列：排序列，要求為數(shù)字
* 第二列：值(值1..值2)
* 第三列：字段名(colNm1..colNm2)
* 當(dāng)然，你不care最后結(jié)果的字段的排序和字段名的話，第一列您直接指定一個固定值就行，第三列跟第二列一樣也行
* as_tableName 指定一個視圖名來存放轉(zhuǎn)換后的數(shù)據(jù),調(diào)用存儲過程后，通過此視圖查詢結(jié)果集
*/
create or replace procedure p_rowToCol(as_sql in varchar2, as_sql_col in varchar2, as_viewName in varchar2) is
lr_curid integer; --游標(biāo)id
ls_cnt number(8); --計數(shù)用
ls_sql varchar2(4000); --sql語句
ls_sql_col varchar2(4000); --同 as_sql_col
ls_rsltTab dbms_sql.desc_tab; --存放返回的結(jié)果集
ls_viewName varchar2(200); --轉(zhuǎn)換結(jié)果存放的表名
ls_guid varchar2(50); --當(dāng)次轉(zhuǎn)換的guid
ls_aggColNm varchar2(50); --對應(yīng)pivot的聚合列的列名
ls_changeColNm varchar2(50); --轉(zhuǎn)換列的列名
ls_cnt_col number(8); --要轉(zhuǎn)換出來的列數(shù)
ls_in_text varchar2(4000); --對應(yīng)for()的內(nèi)容
ls_cnt_end number(8); --最終查詢結(jié)果的列數(shù)
ls_sql_end varchar2(4000); --最終的插入語句
ls_col_add varchar2(4000); --存放轉(zhuǎn)出的列名
ls_col_fixed varchar2(4000); --存放不需要轉(zhuǎn)換的列名
ls_col_insert varchar2(4000); --存放插入的字段
ls_col_view varchar2(4000); --視圖的字段
ls_sql_view varchar2(4000); --存放最后的視圖的sql
ls_thead varchar2(4000); --拼接一個表頭出來，說不定可以回查
begin
--兩步準(zhǔn)備工作，其實如果做過一次，后面的代碼中其實都不需要執(zhí)行這兩步了
--準(zhǔn)備工作1、看下是否存在tmp_RowToCol_XiaoXianNv這個表，首次使用不存在的話建一個(用于存放轉(zhuǎn)換后的數(shù)據(jù))
select count(*) into ls_cnt from all_tables where table_name = upper('tmp_RowToCol_XiaoXianNv');
if ls_cnt = 0 then
ls_sql := 'create table tmp_RowToCol_XiaoXianNv(fguid varchar2(50),fopdt date default sysdate,fbs varchar2(8),';
for i in 1..200 loop
if i = 200 then
ls_sql := ls_sql || 'C' || i || ' varchar2(4000))';
else
ls_sql := ls_sql || 'C' || i || ' varchar2(4000),';
end if;
end loop;
execute immediate ls_sql;
--怕以后數(shù)據(jù)多查詢慢的話還可以建個索引給fguid字段
execute immediate 'create index IDX_ROWSTOCOLS_FGUID on tmp_RowToCol_XiaoXianNv (fguid)';
end if;
--準(zhǔn)備工作2、看下是否存在一個tmp_XiaoXianNv_t1這個臨時表，首次使用不存在的話建一個(用于處理轉(zhuǎn)換列排序)
select count(*) into ls_cnt from all_tables where table_name = upper('tmp_XiaoXianNv_t1');
if ls_cnt = 0 then
ls_sql := 'create global temporary table tmp_XiaoXianNv_t1(fseq NUMBER(20),c1 VARCHAR2(4000),c2 VARCHAR2(4000)) on commit delete rows';
execute immediate ls_sql;
end if;
--取個guid，準(zhǔn)備開干
--這個就是這一次轉(zhuǎn)換的的key，以后要找這次轉(zhuǎn)換的數(shù)據(jù)都可以拿著這個key到tmp_RowToCol_XiaoXianNv找
--所以其實也可以通過傳參來手動指定這個key，然后以后想查回來這次的數(shù)據(jù)都會比較方便
ls_guid := sys_guid();
--獲取轉(zhuǎn)換列和聚合列的列名，即as_sql查詢結(jié)果的倒數(shù)兩列
ls_sql := as_sql;
lr_curid := dbms_sql.open_cursor;
dbms_sql.parse(lr_curid, ls_sql, dbms_sql.native);
dbms_sql.describe_columns(lr_curid, ls_cnt, ls_rsltTab);
ls_changeColNm := ls_rsltTab(ls_cnt - 1).col_name; --倒數(shù)第2列，獲取轉(zhuǎn)換列列名
ls_aggColNm := ls_rsltTab(ls_cnt).col_name; --倒數(shù)第1列，獲取聚合列列名
ls_cnt := ls_cnt - 2; --不需要轉(zhuǎn)換的列數(shù)
--拼接不需要轉(zhuǎn)換的列名，用于后面建視圖(part 1)
for i in 1..ls_cnt loop
ls_col_fixed := ls_col_fixed || ls_rsltTab(i).col_name || ', ';
end loop;
dbms_sql.close_cursor(lr_curid);
--拼接 for xxx in ('值1' as colNm1，'值2' as colNm2 ...) 部分
--獲取所有列名并拼接
--1、先把所有列名的查詢sql搞定
if as_sql_col is null then
ls_sql_col := 'select rownum rn, c1, c1 c2 from (select distinct '|| ls_changeColNm || ' c1 from (' || as_sql || ') order by ' || ls_changeColNm || ')';
else
ls_sql_col := as_sql_col;
end if;
--2、把轉(zhuǎn)換列的數(shù)據(jù)插入到臨時表
execute immediate 'delete from tmp_XiaoXianNv_t1';
ls_sql := 'insert into tmp_XiaoXianNv_t1 (fseq, c1, c2) '|| ls_sql_col;
execute immediate ls_sql;
--3、ls_cnt_col count出要轉(zhuǎn)換出的列數(shù)
execute immediate 'select count(*) from tmp_XiaoXianNv_t1' into ls_cnt_col;
--順便算一下最終查詢結(jié)果的列數(shù)
ls_cnt_end := ls_cnt + ls_cnt_col;
--4、拼接for xx in () 里面的內(nèi)容
ls_sql := 'select listagg(''''''''||c1||'''''' ''||c2 , '', '') within group(order by fseq ) from tmp_XiaoXianNv_t1 a';
execute immediate ls_sql into ls_in_text;
--5、順便拼接出行轉(zhuǎn)列轉(zhuǎn)換出來的字段名，用于后面建視圖(part 2)
ls_sql := 'select listagg(c2,'','')within group(order by fseq) from tmp_XiaoXianNv_t1';
execute immediate ls_sql into ls_col_add;
--拼接插入的表的字段 tmp_RowToCol_XiaoXianNv(c1,c2,c3...)
select listagg(col, ', ') within group(order by rn)
into ls_col_insert
from (select rownum rn, 'c' || rownum col
from dual
connect by rownum <= ls_cnt_end);
--拼接pivot的insert sql，插入內(nèi)容，fbs為標(biāo)識字段，標(biāo)記為1，即為正式數(shù)據(jù)
ls_sql_end := 'insert into tmp_RowToCol_XiaoXianNv (fguid,fbs,' || ls_col_insert || ') '
||'select '''|| ls_guid ||''' fguid,''1'',t.* from ('
|| as_sql || ') PIVOT(max(' || ls_aggColNm || ') for ' || ls_changeColNm || ' in ('
|| ls_in_text || ')) t' ;
execute immediate ls_sql_end;
commit;
--拼接表頭的字段
ls_thead := ls_col_fixed || ls_col_add;
ls_thead := replace(ls_thead,' ');
ls_col_view := ls_thead; --轉(zhuǎn)存一下給下面拼接視圖的使用
select listagg(''''||col||'''',',')within group(order by rn)
into ls_thead from (
select level rn, regexp_substr(ls_thead,'[^,]+',1,level) col
from dual connect by level <= ls_cnt_end
);
--拼接pivot的insert sql，插入內(nèi)容，fbs為標(biāo)識字段，標(biāo)記為轉(zhuǎn)換后的字段數(shù)，即ls_cnt_end變量，即為正式數(shù)據(jù)
ls_sql := 'insert into tmp_RowToCol_XiaoXianNv (fguid,fbs,' || ls_col_insert || ') values('''|| ls_guid ||''','''||ls_cnt_end||''','||ls_thead||')';
execute immediate ls_sql;
commit;
--拿到結(jié)果視圖名，默認為tmp_rowToCol
if as_viewName is null then
ls_viewName := 'tmp_rowToCol';
else
ls_viewName := as_viewName;
end if;
/***************************************這部分的代碼可以直接刪掉************************************
--上面是根據(jù)前面的數(shù)據(jù)拼接出來的視圖的字段ls_col_view，如果我們是只知道一個guid的時候，我們其實也可以去從數(shù)據(jù)表中查出表頭，然后拼接
ls_sql := 'select max(fbs) from tmp_RowToCol_XiaoXianNv where fguid = '''||ls_guid||''' and fbs <> ''1''';
execute immediate ls_sql into ls_cnt_end; --獲取列數(shù)
select listagg(col,'||'',''||')within group(order by rn)
into ls_sql from (
select level rn, 'C'||level col from dual connect by level <= ls_cnt_end
);
ls_sql := 'select '|| ls_sql || ' from tmp_RowToCol_XiaoXianNv where fguid = '''||ls_guid||''' and fbs <> ''1''';
execute immediate ls_sql into ls_col_view; --獲取視圖列名ls_col_view，這里得到的跟上面獲取到的是一樣的
**********************************************************************************************/
--拼接視圖的字段別名轉(zhuǎn)換關(guān)系 c1 字段1, c2 字段2 ...
select listagg(c1||' '||col,',')within group(order by rn)
into ls_col_view from (
select 'C'||level c1, level rn, regexp_substr(ls_col_view,'[^,]+',1,level) col
from dual connect by level <= ls_cnt_end
);
--視圖呈現(xiàn)
ls_sql_view := 'create or replace view '|| ls_viewName ||' as select '|| ls_col_view || ' from tmp_RowToCol_XiaoXianNv where fbs = ''1'' and fguid = '''|| ls_guid || '''';
execute immediate ls_sql_view;
end p_rowToCol;
完結(jié)篇.5?測試SQL
--建表
--drop table SalesList;
create table SalesList(
keHu varchar2(20), --客戶
shangPinId number(8), --商品Id
shangPin varchar2(20), --商品名稱
salesNum number(8) --銷售數(shù)量
);
--插入數(shù)據(jù)
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
--循環(huán)插入
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;
/
--查看下數(shù)據(jù)
select * from salesList a;
--固定行轉(zhuǎn)列
select *
from (select kehu, shangPin, salesNum from salesList) pivot(
max(salesNum) for shangPin in (
'上衣' as 上衣數(shù)量,
'褲子' as 褲子數(shù)量,
'襪子' as 襪子數(shù)量,
'帽子' as 帽子數(shù)量
)
);
--動態(tài)行轉(zhuǎn)列
call p_rowtocol('select keHu, shangPin, salesNum from SalesList',
'',
'Sale_RowToCol');
select * from Sale_RowToCol;
--完整版
call p_rowtocol('select keHu 客戶, shangPin, salesNum from SalesList',
'select distinct shangPinId, shangPin, shangPin||''數(shù)量'' from salesList order by shangPinId',
'Sale_RowToCol');
select * from Sale_RowToCol;
--數(shù)據(jù)存儲的表
select * from tmp_RowToCol_XiaoXianNv
結(jié)束語？沒有
啊對，這個存儲過程中有建表和建視圖的語法，如果你的用戶沒有權(quán)限的話需要用dba用戶給一下權(quán)限：
grant create table to user;
grant create view to user;
================2019年4月25日 更新================
評論區(qū)一個小伙伴報的bug。
拼接出來的sql語句，pivot(xxx for xxx in ('0' 0, '1' 1, '2' 2)) 的這部分，當(dāng)列為純數(shù)字的時候，別名要加個雙引號。
就是說，拼接出來的應(yīng)該是 pivot(xxx for xxx in ('0' "0", '1' "1", '2' "2")) 才對。
修改內(nèi)容：代碼的11行，拼接列別名的時候，添加個雙引號把列名包住
更新后代碼如下：
嗯，再次感謝提bug的小伙伴。
————————————————
版權(quán)聲明：本文為CSDN博主「huay_li」的原創(chuàng)文章，遵循CC 4.0 by-sa版權(quán)協(xié)議，轉(zhuǎn)載請附上原文出處鏈接及本聲明。
原文鏈接：https://blog.csdn.net/huay_li/article/details/82924443
總結(jié)
以上是生活随笔為你收集整理的oracle 动态sql列转行_Oracle 行转列 动态出转换的列的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： python乒乓球比赛规则介绍_乒乓球比
- 下一篇： 泛微e9隐藏明细表_泛微E8 隐藏行、明