Oracle字符串，⾏转列、列转⾏的Sql语句总结多⾏转字符串
这个⽐较简单，⽤||或concat函数可以实现
SQL Code
1 2select concat(id,username) str from app_user select id||username str from app_user
字符串转多列
实际上就是拆分字符串的问题，可以使⽤ substr、instr、regexp_substr函数⽅式
字符串转多⾏
使⽤union all函数等⽅式
wm_concat函数
⾸先让我们来看看这个神奇的函数wm_concat(列名)，该函数可以把列值以","号分隔起来，并显⽰成⼀⾏，接下来上例⼦，看看这个神奇的函数如何应⽤准备测试数据 SQL Code
1 2 3 4 5 6create table test(id number,name varchar2(20)); insert into test values(1,'a');
insert into test values(1,'b');
insert into test values(1,'c');
insert into test values(2,'d');
insert into test values(2,'e');
效果1 : ⾏转列，默认逗号隔开
SQL Code
1select wm_concat(name) name from test;
效果2: 把结果⾥的逗号替换成"|"
SQL Code
1select replace(wm_concat(name),',','|') from test;
效果3: 按ID分组合并name
SQL Code
1select id,wm_concat(name) name from test group by id;
sql语句等同于下⾯的sql语句:
SQL Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36-------- 适⽤范围：8i,9i,10g及以后版本（ MAX + DECODE ）
select id,
max(decode(rn, 1, name, null)) ||
max(decode(rn, 2, ',' || name, null)) ||
max(decode(rn, 3, ',' || name, null)) str
from (select id,
name,
row_number() over(partition by id order by name) as rn
from test) t
group by id
order by1;
-------- 适⽤范围：8i,9i,10g及以后版本（ ROW_NUMBER + LEAD ）
select id, str
from (select id,
row_number() over(partition by id order by name) as rn,
name || lead(',' || name, 1) over(partition by id order by name) ||
lead(',' || name, 2) over(partition by id order by name) ||
lead(',' || name, 3) over(partition by id order by name) as str
from test)
where rn = 1
order by1;
-------- 适⽤范围：10g及以后版本（ MODEL ）
select id, substr(str, 2) str
from test model return updated rows partition by(id) dimension by(row_number()
over(partition by id order by name) as rn) measures(cast(name as varchar2(20)) as str) rules upsert iterate(3) until(presentv(str [ iteration_number + 2 ], 1, 0) = 0)
(str [ 0 ] = str [ 0 ] || ',' || str [ iteration_number + 1 ])
order by1;
-------- 适⽤范围：8i,9i,10g及以后版本（ MAX + DECODE ）
select t.id id, max(substr(sys_connect_by_path(t.name, ','), 2)) str
from (select id, name, row_number() over(partition by id order by name) rn from test) t
start with rn = 1
connect by rn = prior rn + 1
and id = prior id
group by t.id;
懒⼈扩展⽤法:
案例: 我要写⼀个视图，类似"create or replace view as select 字段1,...字段50 from tablename" ，基表有50多个字段，要是靠⼿⼯写太⿇烦了，有没有什么简便的⽅法? 当然有了，看我如果应⽤wm_concat来让这个需求变简单，假设我的APP_USER表中有（id,username,password,age）4个字段。查询结果如下
SQL Code
1 2 3 4 5/** 这⾥的表名默认区分⼤⼩写 */
select'create or replace view as select ' || wm_concat(column_name) || ' from APP_USER' sqlStr
from user_tab_columns
where table_name = 'APP_USER';
利⽤系统表⽅式查询
1select * from user_tab_columns
Oracle 11g ⾏列互换 pivot 和 unpivot 说明
增加字段的sql语句在Oracle 11g中，Oracle ⼜增加了2个查询：pivot（⾏转列）和unpivot（列转⾏）
pivot 列转⾏
测试数据 (id，类型名称，销售数量)，案例：根据⽔果的类型查询出⼀条数据显⽰出每种类型的销售数量。 SQL Code
1 2 3 4 5 6 7 8 9create 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);
分组查询（当然这是不符合查询⼀条数据的要求的）
SQL Code
1select name, sum(nums) nums from demo group by
name
⾏转列查询
SQL Code
1select * from (select name, nums from demo) pivot (sum(nums) for name in ('苹果'苹果, '橘⼦', '葡萄', '芒果'));
注意： pivot（聚合函数 for 列名 in（类型）），其中 in('') 中可以指定别名，in中还可以指定⼦查询，⽐如 select distinct code from customers 当然也可以不使⽤pivot函数，等同于下列语句，只是代码⽐较长，容易理解
SQL Code
1 2 3 4 5select *
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个季度的销售数量，现在要将每种⽔果的每个季度的销售情况⽤多⾏数据展⽰。创建表和数据
1 2 3 4 5 6create 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
列转⾏查询
SQL Code
1select id , name, jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )注意： unpivot没有聚合函数，xiaoshou、jidu
字段也是临时的变量
同样不使⽤unpivot也可以实现同样的效果，只是sql语句会很长，⽽且执⾏速度效率也没有前者⾼
SQL Code
1 2 3 4 5 6 7select id, name ,'Q1' jidu, (select q1 from fruit where id=f.id) xiaoshou from Fruit f union
select id, name ,'Q2' jidu, (select q2 from fruit where id=f.id) xiaoshou from Fruit f union
select id, name ,'Q3' jidu, (select q3 from fruit where id=f.id) xiaoshou from Fruit f union
select id, name ,'Q4' jidu, (select q4 from fruit where id=f.id) xiaoshou from Fruit f
XML类型
上述pivot列转⾏⽰例中，你已经知道了需要查询的类型有哪些，⽤in()的⽅式包含，假设如果您不知道都有哪些值，您怎么构建查询呢？
pivot操作中的另⼀个⼦句 XML 可⽤于解决此问题。该⼦句允许您以 XML 格式创建执⾏了 pivot 操作的输出，在此输出中，您可以指定⼀个特殊的⼦句 ANY ⽽⾮⽂字值⽰例如下：
SQL Code
1 2 3 4 5 6 7select * from (
select name, nums as"Purchase Frequency" from demo t
)
pivot xml (
sum(nums) for name in (any)
)
如您所见，列 NAME_XML 是 XMLTYPE，其中根元素是 <PivotSet>。每个值以名称-值元素对的形式表⽰。您可以使⽤任何 XML 分析器中的输出⽣成更有⽤的输出。
对于该xml⽂件的解析，贴代码如下： SQL Code
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论