原表(sdtd里面10开头的是一类,20开头的是一类、30开头的是一类,按照每一类形成一个新列)
ajbh sdtd
1 101,201
2 102,301
3 202,302
4 101,201,301需要转换成新表
ajbh sdtd1 sdtd2 sdtd3
1 101 201
2 102 301
3 202 302
4 101 201 301请问如何实现,谢谢!
ajbh sdtd
1 101,201
2 102,301
3 202,302
4 101,201,301需要转换成新表
ajbh sdtd1 sdtd2 sdtd3
1 101 201
2 102 301
3 202 302
4 101 201 301请问如何实现,谢谢!
SELECT 1 ajbh,'101,201' sdtd FROM dual UNION ALL
SELECT 2,'102,301' FROM dual UNION ALL
SELECT 3,'202,302' FROM dual UNION ALL
SELECT 4,'101,201,301' FROM dual
)
--以上是测试数据
select ajbh,
Max(Decode(SubStr(sdtd,1,2),'10',sdtd,null)) sdtd1,
Max(Decode(SubStr(sdtd,1,2),'20',sdtd,null)) sdtd2,
Max(Decode(SubStr(sdtd,1,2),'30',sdtd,null)) sdtd3
from(
select distinct ajbh,
substr(','||sdtd||',',instr(','||sdtd||',',',',1,level)+1,
instr(','||sdtd||',',',',1,level+1)-instr(','||sdtd||',',',',1,level)-1) sdtd from tab
connect by level <= length(','||sdtd||',') - length(replace(','||sdtd||',', ',', ''))-1
)
group by ajbh--结果:
AJBH SDTD1 SDTD2 SDTD3
----------------------------
1 101 201
2 102 301
3 202 302
4 101 201 301
SQL>
SQL> with tb as(
2 select 1 ajbh,'101,201' sdtd from dual union all
3 select 2,'102,301' from dual union all
4 select 3,'202,302' from dual union all
5 select 4,'101,201,301' from dual )
6 select ajbh,
7 max(decode(sign(instr(','||sdtd||',',',10')),1,
8 substr(','||sdtd||',',instr(','||sdtd||',',',10')+1,abs(instr(','||sdtd||',',',',instr(','||sdtd||',',',10'),2)-instr(','||sdtd||',',',10',1,1)-1))
9 )) "10开头",
10 max(decode(sign(instr(','||sdtd||',',',20')),1,
11 substr(','||sdtd||',',instr(','||sdtd||',',',20')+1,abs(instr(','||sdtd||',',',',instr(','||sdtd||',',',20'),2)-instr(','||sdtd||',',',20',1,1)-1))
12 )) "20开头",
13 max(decode(sign(instr(','||sdtd||',',',30')),1,
14 substr(','||sdtd||',',instr(','||sdtd||',',',30')+1,abs(instr(','||sdtd||',',',',instr(','||sdtd||',',',30'),2)-instr(','||sdtd||',',',30',1,1)-1))
15 )) "30开头"
16 from tb
17 group by ajbh
18 order by ajbh
19 /
AJBH 10开头 20开头 30开头
---------- -------------------------- -------------------------- --------------------------
1 101 201
2 102 301
3 202 302
4 101 201 301
WITH tab AS(
SELECT 1 ajbh,'101,201' sdtd FROM dual UNION ALL
SELECT 2,'102,301' FROM dual UNION ALL
SELECT 3,'202,302' FROM dual UNION ALL
SELECT 4,'101,201,301' FROM dual UNION ALL
SELECT 4,'101,201,301,401' FROM dual
) -- 查询SQL
select ajbh
,regexp_substr(sdtd||',','.*?'||',',1,1) sdtd_1
,regexp_substr(sdtd||',','.*?'||',',1,2) sdtd_2
,regexp_substr(sdtd||',','.*?'||',',1,3) sdtd_3
,regexp_substr(sdtd||',','.*?'||',',1,4) sdtd_4
from tab;PS.弱弱的问一下,上面的SQL CODE 大框框怎么出来啊?
--回复的时候可以选择插入代码#按钮,然将你代码贴进去
--或者
--直接在回复内容里加入
--[code=SQL] 这个里面就是你代码
[/code]
SQL> with tb as(
2 select 1 ajbh,'101,201' sdtd from dual union all
3 select 2,'102,301,401' from dual union all
4 select 3,'202,302,101' from dual union all
5 select 4,'101,201,301,401' from dual )
6 select ajbh,
7 max(decode(substr(sdtd,1,2),'10',sdtd)),
8 max(decode(substr(sdtd,1,2),'20',sdtd)),
9 max(decode(substr(sdtd,1,2),'30',sdtd)),
10 max(decode(substr(sdtd,1,2),'40',sdtd))
11 from (select distinct ajbh,
12 substr(','||sdtd||',',
13 instr(','||sdtd||',',',',1,level)+1,
14 instr(','||sdtd||',',',',1,level+1)-instr(','||sdtd||',',',',1,level)-1) sdtd
15 from tb
16 connect by level<=length(','||sdtd||',')-length(replace(','||sdtd||',',',',''))-1)
17 group by ajbh
18 /
AJBH MAX(DECODE(SUBSTR(SDTD,1,2),'1 MAX(DECODE(SUBSTR(SDTD,1,2),'2 MAX(DECODE(SUBSTR(SDTD,1,2),'3 MAX(DECODE(SUBSTR(SDTD,1,2),'4
---------- ---------------------------------- ---------------------------------- ---------------------------------- ----------------------------------
1 101 201
2 102 301 401
4 101 201 301 401
3 101 202 302
SQL>