列转行

oracle一个表或者视图中有 a,b字段，如下面补充，求sql语句，谢谢
比如
         a          b
      北京       故宫
      北京       颐和园
      北京       长城
      上海       外滩
      上海       浦东
      上海       故宫
      ......
1.先显示: 北京，故宫、颐和园、长城
              上海，外滩、浦东、故宫

 

create table test_tab(province varchar2(20),landscapes varchar2(50));
insert into test_tab values('北京','故宫');
insert into test_tab values('北京','颐和园');
insert into test_tab values('北京','长城');
insert into test_tab values('上海','外滩');
insert into test_tab values('上海','浦东');
insert into test_tab values('上海','世博园');
insert into test_tab values('四川','蜀南竹海');

 

select * from test_tab;
--------------------------------------
           PROVINCE        LANDSCAPES
1        北京        故宫
2        北京        颐和园
3        北京        长城
4        上海        外滩
5        上海        浦东
6        上海        世博园
7        四川        蜀南竹海
--------------------------------------
SELECT province, SUBSTR(MAX(SYS_CONNECT_BY_PATH(landscapes, ',')), 2) NAME
  FROM (SELECT province,
               landscapes,
               rn,
               LEAD(rn) OVER(PARTITION BY province ORDER BY rn) rn1
          FROM (SELECT province, landscapes, ROW_NUMBER() OVER(ORDER BY landscapes) rn
                  FROM test_tab))
START WITH rn1 IS NULL
CONNECT BY rn1 = PRIOR rn
GROUP BY province;

----------------------------------------
           PROVINCE        NAME
1        北京                  颐和园,故宫,长城
2        上海                  外滩,世博园,浦东
3        四川                  蜀南竹海

 

Oracle函数sys_connect_by_path 详解：
语法：
Oracle函数:sys_connect_by_path 主要用于树查询(层次查询) 以及 多列转行。其语法一般为：
       select ... sys_connect_by_path(column_name,'connect_symbol')  from table
       start with ... connect by ... prior
理解：
对于数据库来说，根节点并不一定是在数据库中设计的顶级节点，而是start with开始的地方。sys_connect_by_path函数就是从start with开始的地方开始遍历，并记下其遍历到的节点，start with开始的地方被视为根节点，将遍历到的路径根据函数中的分隔符，组成一个新的字符串。sys_connect_by_path函数用connect by来寻找下一条记录，直到迭代找不到相应记录为止。概念与递归类似，connect by指定递归(连接)条件，如果条件不满足则递归结束。

 

 

结束语：

其实可以用一条语句实现，不过要在10G以上的版本才可以，SQL如下：

select t.place,wm_concat(t.jing)
from test_tab t
group by t.place;

 

 

 

评论

2 楼 zhengyong7232 2012-11-28  

SELECT t.province,
DECODE(landscapes, '故宫', T.landscapes, '' ) a1,
DECODE(landscapes, '颐和园', T.landscapes, '')  a2,
DECODE(landscapes, '长城', T.landscapes,'' ) a3
FROM test_tab T

SELECT t.province,
max(DECODE(landscapes, '故宫', T.landscapes, '')) a1,
max(DECODE(landscapes, '颐和园', T.landscapes, '')) a2,
max(DECODE(landscapes, '长城', T.landscapes,'')) a3
FROM test_tab T
GROUP BY t.province


select a.province,a.a1||','||a.a2||a.a3 as aa from
(SELECT t.province,
max(DECODE(landscapes, '故宫', T.landscapes, '')) a1,
max(DECODE(landscapes, '颐和园', T.landscapes, '')) a2,
max(DECODE(landscapes, '长城', T.landscapes,'')) a3
FROM test_tab T
GROUP BY t.province) a

1 楼 meepo3927 2012-07-12  

不是特么很懂