如何列变行,去除重复数据
例 表名 a_ac
字段名 hm a1 b1 c1
01 01,02 aa,ac,ag 001,002
01 01,03 ac.df 002,003,004
02 11,12,13 ab,as,ac 010,011,009
02 23,12,23 sw,as,ab 032,023
显示结果:
hm a1 b1 c1
01 01,02,03 aa,ac,ag,df 001,002,003,004
02 11,12,13,23 ab,as,ac,sw, 010,011,009,032,023
例 表名 a_ac
字段名 hm a1 b1 c1
01 01,02 aa,ac,ag 001,002
01 01,03 ac.df 002,003,004
02 11,12,13 ab,as,ac 010,011,009
02 23,12,23 sw,as,ab 032,023
显示结果:
hm a1 b1 c1
01 01,02,03 aa,ac,ag,df 001,002,003,004
02 11,12,13,23 ab,as,ac,sw, 010,011,009,032,023
因为你这个要先行转列,去重,然后再列转行啊
1.行转列
这是一个行转列例子,替换成你的表和字段
select a,b,c from
(with test as (select 'aaa' a,'bbb' b,'1,2,3' c from dual)
select a,b,substr(t.ca,instr(t.ca, ',', 1, c.lv) + 1,instr(t.ca, ',', 1, c.lv + 1) - (instr(t.ca, ',', 1, c.lv) + 1)) AS c
from (select a,b,',' || c || ',' AS ca,length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt FROM test) t,
(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c where c.lv <= t.cnt )2.去重
去重就不说了,方法很多,distinct之类的就行
在上面取出的结果集外面用select distinct检索一下就可以了
3.列转行
用wm_concat函数直接转就行了
对于上面的结果集用下面的sql在字查询一下就可以了
select hm, wm_concat(a1),wm_concat(b1),wm_concat(c1) from table group by hm
主键:id+lx
select * from tab_1;显示结果如下:
id lx sj
---------------------
1 1类 03-01-01
1 2类 03-01-02
2 1类 03-02-01
2 2类 03-02-02
2 3类 03-02-03
3 2类 03-03-02 select id,max(decode(lx,'1类',sj)) 1类,
max(decode(lx,'2类',sj)) 2类,
max(decode(lx,'3类',sj)) 3类
from tab_1
group by id; id 1类 2类 3类
-------------------------------------------
1 03-01-01 03-01-02
2 03-02-01 03-02-02 03-02-03
3 03-03-02
..........