『壹』 Oracle中列轉行，如何實現
基本思路：對每班學生排序，根據序號構造列名，拼接動態sql
--測試數據
createtable
("學生"varchar2(10)
,"學號"varchar2(10)
,"班級"varchar2(10)
);
insertinto"表A"
select'張三','100','一班'fromalunionall
select'李四','101','二班'fromalunionall
select'王五','102','一班'fromalunionall
select'趙六','103','三班'fromalunionall
select'李二','104','二班'fromal
--動態拼接Pivot
declare
sqlstrvarchar2(8000):='';
begin
--構造類似於'學號1','學號2',...的字元串
forxin(
selectdistinctrow_number()over(partitionby"班級"orderby"學號")seq
from"表A"orderbyseq)loop
sqlstr:=sqlstr||','''||'學號'||to_char(x.seq)||'''';
endloop;
sqlstr:=substr(sqlstr,2,length(sqlstr)-1);
--將前面構造的字元串放入Pivot語句中
sqlstr:='
select*from(
select"學號","班級",''學號''||to_char(
row_number()over(partitionby"班級"orderby"學號"))seq
from"表A")t
pivot(
max("學號")
forseqin('||sqlstr||')
)';
--dbms_output.put_line(sqlstr);
--將查詢結果放入臨時視圖中
sqlstr:='CREATEORREPLACEVIEWtmp_resultAS'||sqlstr;
--dbms_output.put_line(sqlstr);
executeimmediatesqlstr;
end;
--查看結果
select*fromtmp_result;
結果如下：
『貳』 ORACLE 同表列轉行
createtabletest(a1number,a2number,a3number);
insertintotestvalues(1,2,3);
insertintotestvalues(11,12,13);
dexter@REPO>selectafrom(
2SELECT*
3FROMtest
4UNPIVOT(
5a
6forv
7IN(a1,a2,a3)
8));
A
----------
1
2
3
11
12
13
已選擇6行。
用的是11g列轉行函數，unpovit。 這些列的數據類型必須是一致的。試一下吧。
『叄』 Oracle列轉行的問題
如果單純是你表裡數據的話：
創建表，數據
createtabletest
(keyvarchar2(20),
valuevarchar2(20));
insertintotestvalues('account','0001');
insertintotestvalues('name','張三');
insertintotestvalues('account','0002');
insertintotestvalues('name','李四');
commit;
執行：
selectmax(case
whent.key='account'then
value
end)account,
max(case
whent.key='name'then
value
end)name
from(selecttest.*,rownumrnfromtest)t
groupbyceil(rn/2)
結果：
我這里是強行把行號作為了分組條件，否則同樣的0001賬戶也有可能對應上李四這個人，所以你最好自己弄清楚怎麼才是一組，比如是一組的兩條數據給個同樣的ID之類的。
『肆』 oracle的sql語句列轉行
不同人的uuid是不一樣的嗎？
select
(selectzfromtabnameaawherezmc='姓名'andaa.uuid=a.uuid)姓名,
(selectzfromtabnameaawherezmc='年齡'andaa.uuid=a.uuid)年齡,
(selectzfromtabnameaawherezmc='英文名稱'andaa.uuid=a.uuid)英文名稱,
(selectzfromtabnameaawherezmc='性別'andaa.uuid=a.uuid)性別,
(selectzfromtabnameaawherezmc='入職日期'andaa.uuid=a.uuid)入職日期,
(selectzfromtabnameaawherezmc='個人信息'andaa.uuid=a.uuid)個人信息
from(selectdistinctuuidfromtabname)a
『伍』 oracle的clob欄位進行列轉行
你認為varchar2能處理，把CLOB欄位做個轉換，中間加個過渡表，可以使用dbms_lob.substr函數。
『陸』 Oracle列轉行，行轉列
oracle下可以用函數decode處理:
select 產品名稱,
sum(decode(季度,'第一季度',銷售額,0)) 第一季度銷售額,
sum(decode(季度,'第二季度',銷售額,0)) 第二季度銷售額,
sum(decode(季度,'第三季度',銷售額,0)) 第三季度銷售額,
sum(decode(季度,'第四季度',銷售額,0)) 第四季度銷售額,
from 表名
group by 產品名稱;
『柒』 oracle 列轉行
SQL> create table t (a number, b varchar2(10));
表已創建。
SQL> insert into t values(1,'A');
已創建 1 行。
SQL> insert into t values(1,'B');
已創建 1 行。
SQL> insert into t values(2,'A');
已創建 1 行。
SQL> insert into t values(2,'B');
已創建 1 行。
SQL> insert into t values(3,'C');
已創建 1 行。
SQL> insert into t values(3,'F');
已創建 1 行。
SQL> insert into t values(4,'D');
已創建 1 行。
SQL> commit;
提交完成。
SQL> select a,max(decode(c,1,b,null)),
2 max(decode(c,2,b,null)),
3 max(decode(c,3,b,null))
4 from(select a,b,row_number()over(partition by a order by b ) c from t)
5* group by a
SQL> /
A MAX(DECODE MAX(DECODE MAX(DECODE
---------- ---------- ---------- ----------
1 A B
2 A B
3 C F
4 D
SQL>
『捌』 oracle 多列 列轉行
Oracle11g 行列互換 pivot 和 unpivot 說明在Oracle 11g中，Oracle 又增加了2個查詢：pivot（行轉列） 和unpivot（列轉行）
參考：https://blog.csdn.net/tianlesoftware/article/details/7060306、https://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.htmlgoogle 一下，網上有一篇比較詳細的文檔：https://www.oracle-developer.net/display.php?id=506
pivot 列轉行
測試數據 (id，類型名稱，銷售數量)，案例：根據水果的類型查詢出一條數據顯示出每種類型的銷售數量。
?
123456789create table demo(id int,name varchar(20),nums int); ---- 創建表insert into demo values(1, '蘋果', 1000);insert into demo values(2, '蘋果', 2000);insert into demo values(3, '蘋果', 4000);insert into demo values(4, '橘子', 5000);insert into demo values(5, '橘子', 3000);insert into demo values(6, '葡萄', 3500);insert into demo values(7, '芒果', 4200);insert into demo values(8, '芒果', 5500);分組查詢 （當然這是不符合查詢一條數據的要求的）
?
1select name, sum(nums) nums from demo group by name行轉列查詢
?
1select * from (select name, nums from demo) pivot (sum(nums) for name in ('蘋果' 蘋果, '橘子', '葡萄', '芒果'));注意： pivot（聚合函數 for 列名 in（類型）） ，其中 in(『』) 中可以指定別名，in中還可以指定子查詢，比如 select distinct code from customers
當然也可以不使用pivot函數，等同於下列語句，只是代碼比較長，容易理解
?
12select * from （select sum(nums) 蘋果 from demo where name='蘋果'）,（select sum(nums) 橘子 from demo where name='橘子'）,（select sum(nums) 葡萄 from demo where name='葡萄'）,（select sum(nums) 芒果 from demo where name='芒果'）;unpivot 行轉列
顧名思義就是將多列轉換成1列中去
案例：現在有一個水果表，記錄了4個季度的銷售數量，現在要將每種水果的每個季度的銷售情況用多行數據展示。
創建表和數據
?
1234567create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);insert into Fruit values(1,'蘋果',1000,2000,3300,5000);insert into Fruit values(2,'橘子',3000,3000,3200,1500);insert into Fruit values(3,'香蕉',2500,3500,2200,2500);insert into Fruit values(4,'葡萄',1500,2500,1200,3500);select * from Fruit列轉行查詢
?
1select id , name, ji, xiaoshou from Fruit unpivot (xiaoshou for ji in (q1, q2, q3, q4) )注意： unpivot沒有聚合函數，xiaoshou、ji欄位也是臨時的變數
『玖』 oracle行轉列 列轉行求助
Oracle需要首先在資料庫中，創建好類型與函數。
來實現一個split功能的處理。--定義一個對象類型.
CREATEORREPLACETYPEty_row_str_splitasobject(strValueVARCHAR2(4000));
/
--定義一個表/數組類型，內容是前面定義的那個對象.
CREATEORREPLACETYPEty_tbl_str_splitISTABLEOFty_row_str_split;
/
--------------------
--字元分割函數.
--參數1：被分割的源字元串
--參數2：用於拆分的字元串。
--------------------
CREATEORREPLACEFUNCTIONfn_split(
p_strINVARCHAR2,
p_delimiterINVARCHAR2)
RETURNty_tbl_str_splitIS
jINT:=0;
iINT:=1;
--被分割的源字元串的長度.
lenINT:=0;
--分隔字元串的長度
len1INT:=0;
--暫存的中間每一個單元的文本信息.
strVARCHAR2(4000);
--預期返回結果.
str_splitty_tbl_str_split:=ty_tbl_str_split();
BEGIN
--被分割的源字元串的長度.
len:=LENGTH(p_str);
--分隔字元串的長度.
len1:=LENGTH(p_delimiter);
--遍歷被分割的源字元串.
WHILEj<lenLOOP
--在被分割的源字元串中，查詢分隔字元串.
j:=INSTR(p_str,p_delimiter,i);
IFj=0THEN
--j=0意味著沒有找到.
--可以理解為是查詢到最後一個單元了.
--設置j:=len，讓外部的循環處理可以結束了.
j:=len;
--獲取最後一個單元的內容.
str:=SUBSTR(p_str,i);
--結果追加一行.
str_split.EXTEND;
--設置結果內容.
str_split(str_split.COUNT):=ty_row_str_split(strValue=>str);
IFi>=lenTHEN
EXIT;
ENDIF;
ELSE
--如果在被分割的源字元串中，找到了分隔字元串.
--首先，獲取分割的內容.
str:=SUBSTR(p_str,i,j-i);
--然後設置索引，下一次再查找的時候，從指定的索引位置開始（不是從0開始找了）
i:=j+len1;
--結果追加一行.
str_split.EXTEND;
--設置結果內容.
str_split(str_split.COUNT):=ty_row_str_split(strValue=>str);
ENDIF;
ENDLOOP;
RETURNstr_split;
ENDfn_split;
/
函數創建完畢以後，可以開始做查詢的處理.
CREATETABLEa(
idint,
valvarchar2(10)
);
insertintoavalues(1,'B1||B2||B3');
insertintoavalues(2,'B4||B5');
insertintoavalues(3,'B6');
COLUMN"Value"FORMATA15
SQL>select
2a.id,
3to_char(strvalue)asValue
4from
5a,
6table(fn_split(a.val,'||'));
IDVALUE
-------------------------
1B1
1B2
1B3
2B4
2B5
3B6
已選擇6行。
『拾』 關於ORACLE列轉行的問題
先將varchar2轉換為clob再轉換為blob。
http://blog.csdn.net/wbo112/article/details/9041575。
希望能解決你的問題。
--准備數據
createtablea(bhnumber,sjdate,slnumber);
createtableb(bhnumber,zjblob);
insertintoavalues(1001,sysdate,30);
insertintoavalues(1002,sysdate,31);
insertintoavalues(1001,sysdate,32);
insertintoavalues(1003,sysdate,34);
insertintoavalues(1003,sysdate,35);
insertintoavalues(1004,sysdate,36);
insertintoavalues(1004,sysdate,38);
insertintoavalues(1005,sysdate,23);
insertintoavalues(1005,sysdate,30);
insertintoavalues(1006,sysdate,35);
insertintoavalues(1006,sysdate,30);
insertintoavalues(1001,sysdate,30);
--這個方法不知道能否解決你這個4k限制的問題
createorreplaceprocereinsert_bis
cursora_cursorisselect*fromaorderbybh;
a_recorda%rowtype;
temp_bha.bh%type:=0;
temp_zjvarchar2(32767);
begin
opena_cursor;
loop
fetcha_cursorintoa_record;
--插入最後的一條記錄
ifa_cursor%notfoundthen
insertintobvalues(temp_bh,c2b(to_clob(temp_zj)));
exit;
endif;
iftemp_bh!=a_record.bhthen
--插入上一條的記錄值
iftemp_bh!=0then
insertintobvalues(temp_bh,c2b(to_clob(temp_zj)));
endif;
temp_bh:=a_record.bh;
--temp_zj:=concat(concat(concat(concat(concat(concat('編號：',a_record.bh),'，時間：'),to_char(a_record.sj,'yyyy-mm-dd')),'，數量：'),a_record.sl),'；');
temp_zj:='編號：'||a_record.bh||'，時間：'||to_char(a_record.sj,'yyyy-mm-dd')||'，數量：'||a_record.sl||'；';
elsiftemp_bh=a_record.bhthen
--temp_zj:=concat(concat(concat(concat(concat(temp_zj,'時間：'),to_char(a_record.sj,'yyyy-mm-dd')),'，數量：'),a_record.sl),'；');
temp_zj:=temp_zj||'時間：'||to_char(a_record.sj,'yyyy-mm-dd')||'，數量：'||a_record.sl||'；';
endif;
endloop;
closea_cursor;
end;
/
--了解了下listagg函數，這個比較簡潔，不知道會不會出現你所說的4k限制問題。由你這個問題也讓我學到了11g的新函數
createorreplaceprocereinsert_bis
cursora_cursoris
selectbh,
'編號：'||bh||'，'||
listagg('時間：'||to_char(sj,'yyyy-mm-dd')||'，數量：'||sl,
'；')withingroup(orderbybh)||'。'asres
froma
groupbybh;
v_bhnumber;
v_resvarchar2(32767);
begin
opena_cursor;
loop
fetcha_cursor
intov_bh,v_res;
exitwhena_cursor%notfound;
--insertintobvalues(v_bh,c2b(to_clob(v_res)));
insertintobvalues(v_bh,to_blob(rawtohex(v_res)));
endloop;
closea_cursor;
end;
/
--更新數據
begin
insert_b;
end;
/
--查詢結果
selectbh,to_char(b2c(zj))fromb;
--將clob類型轉換為blob類型（二進制轉換）
createorreplacefunctionc2b(srcclobdefaultempty_clob())returnblobis
destblob;
src_lennumber:=dbms_lob.getlength(src);
dest_offsetnumber:=1;
src_offsetnumber:=1;
amount_cinteger:=dbms_lob.lobmaxsize;
blob_csidnumber:=dbms_lob.default_csid;
lang_ctxinteger:=dbms_lob.default_lang_ctx;
warninginteger;
begin
ifsrc_len>0then
--將dest建立在用戶的臨時表空間中，true表示將dest讀到緩沖區。此處相當於初始化dest
dbms_lob.createtemporary(dest,true);
--以readwrite模式打開dest
dbms_lob.open(dest,dbms_lob.lob_readwrite);
--讀取src，轉換字元數據為特定字元集格式，並將轉換後的數據寫入dest中
dbms_lob.converttoblob(dest,--目標blob
src,--源clob
amount_c,--指定要轉換的位元組數
dest_offset,--指定目標lob的偏移位置（位元組或字元）
src_offset,--指定源lob的偏移位置（位元組或字元）
blob_csid,--指定字元集標識號
lang_ctx,--指定語言上下文
warning);--存放警告信息
else
selectempty_blob()intodestfromal;
endif;
returndest;
endc2b;
/
--將blob類型轉換為clob類型
--varchar2類型可直接轉換為clob類型
createorreplacefunctionb2c(srcblob)returnclobis
destvarchar2(32767);
tempvarchar2(32767);
v_startpls_integer:=1;
v_bufferpls_integer:=4000;
begin
ifdbms_lob.getlength(src)isnullthen
return'';
endif;
dest:='';
foriin1..ceil(dbms_lob.getlength(src)/v_buffer)loop
--當轉換出來的字元串亂碼時，可嘗試使用注釋掉的函數
--temp:=utl_raw.cast_to_varchar2(utl_raw.convert(dbms_lob.substr(src,v_buffer,v_start),'SIMPLIFIEDCHINESE_CHINA.ZHS16GBK','AMERICAN_THENETHERLANDS.UTF8'));
temp:=utl_raw.cast_to_varchar2(dbms_lob.substr(src,v_buffer,v_start));
dest:=dest||temp;
v_start:=v_start+v_buffer;
endloop;
returndest;
endb2c;
/