oracle数据库列转行

‘壹’ 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;
/