Oracle行转列、列转行的Sql语句总结

原文地址:http://www.maomao365.com/?p=7307

多行转字符串

这个比较简单,用||或concat函数可以实现

 SQL
Code 

1
2

  

select concat(id,username) str from app_user
select id||username str from app_user

摘要:

字符串转多列

www.463.com,实际上就是拆分字符串的问题,可以使用
substr、instr、regexp_substr函数方式

 
 以前分割字符串时,都使用类似split函数的方式处理,下文分享一种对有规律的字符串的分隔方式,

字符串转多行

使用union
all函数等方式

 
 即:1.使用replace函数,将字符串变成合规的xml格式串

wm_concat函数

首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以”,”号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用准备测试数据

 SQL
Code 

1
2
3
4
5
6

  

create 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’);

     
 2.使用xml.value的方式获取指定的字符串信息

效果1 : 行转列 ,默认逗号隔开

 SQL
Code 

1

  

select wm_concat(name) name from test;

   

www.463.com 1

    如下所示:

效果2: 把结果里的逗号替换成”|”

 SQL
Code 

1

  

select replace(wm_concat(name),’,’,’|’) from test;

   

www.463.com 2

    实验环境:sqlserver 2008 R2  

效果3: 按ID分组合并name

 SQL
Code 

1

  

select id,wm_concat(name) name from test group by id;

   

www.463.com 3

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 by 1;
——– 适用范围: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 by 1;
——– 适用范围: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 by 1;
——– 适用范围: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;

   

发表评论

电子邮件地址不会被公开。 必填项已用*标注