关于ORACLE列转行的问题
发布网友
发布时间：2022-04-22 09:01
共3个回答
热心网友
时间：2022-04-08 12:40
先将varchar2转换为clob再转换为blob。
http://blog.csdn.net/wbo112/article/details/9041575。
希望能解决你的问题。
--准备数据create table a(bh number, sj date, sl number);
create table b(bh number, zj blob);
insert into a values(1001, sysdate, 30);
insert into a values(1002, sysdate, 31);
insert into a values(1001, sysdate, 32);
insert into a values(1003, sysdate, 34);
insert into a values(1003, sysdate, 35);
insert into a values(1004, sysdate, 36);
insert into a values(1004, sysdate, 38);
insert into a values(1005, sysdate, 23);
insert into a values(1005, sysdate, 30);
insert into a values(1006, sysdate, 35);
insert into a values(1006, sysdate, 30);
insert into a values(1001, sysdate, 30);
--这个方法不知道能否解决你这个4k*的问题
create or replace procere insert_b is
cursor a_cursor is select * from a order by bh;
a_record a%rowtype;
temp_bh a.bh%type := 0;
temp_zj varchar2(32767);
begin
open a_cursor;
loop
fetch a_cursor into a_record;
--插入最后的一条记录
if a_cursor%notfound then
insert into b values(temp_bh, c2b(to_clob(temp_zj)));
exit;
end if;
if temp_bh != a_record.bh then
--插入上一条的记录值
if temp_bh != 0 then
insert into b values(temp_bh, c2b(to_clob(temp_zj)));
end if;
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 || '；';
elsif temp_bh = a_record.bh then
--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 || '；';
end if;
end loop;
close a_cursor;
end;
/
--了解了下listagg函数，这个比较简洁，不知道会不会出现你所说的4k*问题。由你这个问题也让我学到了11g的新函数
create or replace procere insert_b is
cursor a_cursor is
select bh,
'编号：' || bh || '，' ||
listagg('时间：' || to_char(sj, 'yyyy-mm-dd') || '，数量：' || sl,
'；') within group(order by bh) || '。' as res
from a
group by bh;
v_bh number;
v_res varchar2(32767);
begin
open a_cursor;
loop
fetch a_cursor
into v_bh, v_res;
exit when a_cursor%notfound;
--insert into b values (v_bh, c2b(to_clob(v_res)));
insert into b values(v_bh, to_blob(rawtohex(v_res)));
end loop;
close a_cursor;
end;
/
--更新数据
begin
insert_b;
end;
/
--查询结果
select bh, to_char(b2c(zj)) from b;
--将clob类型转换为blob类型（二进制转换）
create or replace function c2b(src clob default empty_clob()) return blob is
dest blob;
src_len number := dbms_lob.getlength(src);
dest_offset number := 1;
src_offset number := 1;
amount_c integer := dbms_lob.lobmaxsize;
blob_csid number := dbms_lob.default_csid;
lang_ctx integer := dbms_lob.default_lang_ctx;
warning integer;
begin
if src_len > 0 then
--将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
select empty_blob() into dest from al;
end if;
return dest;
end c2b;
/
--将blob类型转换为clob类型
--varchar2类型可直接转换为clob类型
create or replace function b2c(src blob) return clob is
dest varchar2(32767);
temp varchar2(32767);
v_start pls_integer := 1;
v_buffer pls_integer := 4000;
begin
if dbms_lob.getlength(src) is null then
return '';
end if;
dest := '';
for i in 1..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), 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'AMERICAN_THE NETHERLANDS.UTF8'));
temp := utl_raw.cast_to_varchar2(dbms_lob.substr(src, v_buffer, v_start));
dest := dest || temp;
v_start := v_start + v_buffer;
end loop;
return dest;
end b2c;
/
热心网友
时间：2022-04-08 13:58
在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特发贴集思广义。
http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e14aaee5ab.html?96198
现整理解法如下：
数据样本：
create table tx(
id int primary key,
c1 char(2),
c2 char(2),
c3 int
);
insert into tx values
(1 ,'A1','B1',9),
(2 ,'A2','B1',7),
(3 ,'A3','B1',4),
(4 ,'A4','B1',2),
(5 ,'A1','B2',2),
(6 ,'A2','B2',9),
(7 ,'A3','B2',8),
(8 ,'A4','B2',5),
(9 ,'A1','B3',1),
(10 ,'A2','B3',8),
(11 ,'A3','B3',8),
(12 ,'A4','B3',6),
(13 ,'A1','B4',8),
(14 ,'A2','B4',2),
(15 ,'A3','B4',6),
(16 ,'A4','B4',9),
(17 ,'A1','B4',3),
(18 ,'A2','B4',5),
(19 ,'A3','B4',2),
(20 ,'A4','B4',5);
mysql> select * from tx;
+----+------+------+------+
| id | c1 | c2 | c3 |
+----+------+------+------+
| 1 | A1 | B1 | 9 |
| 2 | A2 | B1 | 7 |
| 3 | A3 | B1 | 4 |
| 4 | A4 | B1 | 2 |
| 5 | A1 | B2 | 2 |
| 6 | A2 | B2 | 9 |
| 7 | A3 | B2 | 8 |
| 8 | A4 | B2 | 5 |
| 9 | A1 | B3 | 1 |
| 10 | A2 | B3 | 8 |
| 11 | A3 | B3 | 8 |
| 12 | A4 | B3 | 6 |
| 13 | A1 | B4 | 8 |
| 14 | A2 | B4 | 2 |
| 15 | A3 | B4 | 6 |
| 16 | A4 | B4 | 9 |
| 17 | A1 | B4 | 3 |
| 18 | A2 | B4 | 5 |
| 19 | A3 | B4 | 2 |
| 20 | A4 | B4 | 5 |
+----+------+------+------+
20 rows in set (0.00 sec)
mysql>
期望结果
+------+-----+-----+-----+-----+------+
|C1 |B1 |B2 |B3 |B4 |Total |
+------+-----+-----+-----+-----+------+
|A1 |9 |2 |1 |11 |23 |
|A2 |7 |9 |8 |7 |31 |
|A3 |4 |8 |8 |8 |28 |
|A4 |2 |5 |6 |14 |27 |
|Total |22 |24 |23 |40 |109 |
+------+-----+-----+-----+-----+------+
1. 利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total
mysql> SELECT
-> IFNULL(c1,'total') AS total,
-> SUM(IF(c2='B1',c3,0)) AS B1,
-> SUM(IF(c2='B2',c3,0)) AS B2,
-> SUM(IF(c2='B3',c3,0)) AS B3,
-> SUM(IF(c2='B4',c3,0)) AS B4,
-> SUM(IF(c2='total',c3,0)) AS total
-> FROM (
-> SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) AS c3
-> FROM tx
-> GROUP BY c1,c2
-> WITH ROLLUP
-> HAVING c1 IS NOT NULL
-> ) AS A
-> GROUP BY c1
-> WITH ROLLUP;
+-------+------+------+------+------+-------+
| total | B1 | B2 | B3 | B4 | total |
+-------+------+------+------+------+-------+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| total | 22 | 24 | 23 | 40 | 109 |
+-------+------+------+------+------+-------+
5 rows in set, 1 warning (0.00 sec)
2. 利用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total
mysql> select c1,
-> sum(if(c2='B1',C3,0)) AS B1,
-> sum(if(c2='B2',C3,0)) AS B2,
-> sum(if(c2='B3',C3,0)) AS B3,
-> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
-> from tx
-> group by C1
-> UNION
-> SELECT 'TOTAL',sum(if(c2='B1',C3,0)) AS B1,
-> sum(if(c2='B2',C3,0)) AS B2,
-> sum(if(c2='B3',C3,0)) AS B3,
-> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) FROM TX
-> ;
+-------+------+------+------+------+-------+
| c1 | B1 | B2 | B3 | B4 | TOTAL |
+-------+------+------+------+------+-------+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| TOTAL | 22 | 24 | 23 | 40 | 109 |
+-------+------+------+------+------+-------+
5 rows in set (0.00 sec)
mysql>
3. 利用SUM(IF()) 生成列,直接生成结果不再利用子查询
mysql> select ifnull(c1,'total'),
-> sum(if(c2='B1',C3,0)) AS B1,
-> sum(if(c2='B2',C3,0)) AS B2,
-> sum(if(c2='B3',C3,0)) AS B3,
-> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
-> from tx
-> group by C1 with rollup ;
+--------------------+------+------+------+------+-------+
| ifnull(c1,'total') | B1 | B2 | B3 | B4 | TOTAL |
+--------------------+------+------+------+------+-------+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| total | 22 | 24 | 23 | 40 | 109 |
+--------------------+------+------+------+------+-------+
5 rows in set (0.00 sec)
mysql>
4. 动态，适用于列不确定情况，
mysql> SET @EE='';
mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;
mysql> SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
Query OK, 0 rows affected (0.00 sec)
mysql> PREPARE stmt2 FROM @QQ;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt2;
+--------------------+------+------+------+------+-------+
| ifnull(c1,'total') | B1 | B2 | B3 | B4 | TOTAL |
+--------------------+------+------+------+------+-------+
| A1 | 9 | 2 | 1 | 11 | 23 |
| A2 | 7 | 9 | 8 | 7 | 31 |
| A3 | 4 | 8 | 8 | 8 | 28 |
| A4 | 2 | 5 | 6 | 14 | 27 |
| total | 22 | 24 | 23 | 40 | 109 |
+--------------------+------+------+------+------+-------+
5 rows in set (0.00 sec)
mysql>
希望能帮的上你........
热心网友
时间：2022-04-08 15:33
这个是构造的表，你看下能能不能帮到你。
--构造表结构
/*create table xx_test(
x_num varchar2(50),
x_date date,
x_qty number,
x_blob blob
) ;*/
/*truncate table xx_test ;*/
--插入数据
insert into xx_test(x_num,x_date,x_qty,x_blob) select '1001',to_date('2014-06-01','yyyy-mm-dd'),30,empty_blob() from al;
insert into xx_test(x_num,x_date,x_qty,x_blob) select '1001',to_date('2014-06-25','yyyy-mm-dd'),10,empty_blob() from al;
insert into xx_test(x_num,x_date,x_qty,x_blob) select '1002',to_date('2014-06-15','yyyy-mm-dd'),35,empty_blob() from al;
insert into xx_test(x_num,x_date,x_qty,x_blob) select '1002',to_date('2014-06-13','yyyy-mm-dd'),17,empty_blob() from al;
commit;
--将合并后的数据更新到blob列
update xx_test xx
set x_blob =
(select to_blob(UTL_RAW.cast_to_raw（
'编号：' || t1.x_num || ',时间： ' || t1.x_date ||
',数量： ' || t1.x_qty || ' ; ' || '时间： ' ||
t2.x_date || ',数量： ' || t2.x_qty) ）
from (select t.rowid,
t.*,
LAG(t.x_num) OVER(PARTITION BY t.x_num ORDER BY t.x_num) RNNEXT
from xx_test t) t1,
xx_test t2
where t1.x_num = t2.x_num
and t1.RNNEXT is null
and t2.rowid <> t1.rowid
and t1.x_num = xx.x_num);
commit;
--结果
select * from xx_test;
