“请问各位大侠,能不能转化成这样的结构……”
select id,'a' problemtype from table where problemtype like '%a%'
union all
select id,'b' problemtype from table where problemtype like '%b%'
union all
select id,'c' problemtype from table where problemtype like '%c%'
union all
select id,'d' problemtype from table where problemtype like '%d%'
order by id,problemtype
“或者是这样:……”
select id ,
(case when problemtype like '%a%' then 'a' end)p1,
(case when problemtype like '%b%' then 'b' end)p2,
(case when problemtype like '%c%' then 'c' end)p3,
(case when problemtype like '%d%' then 'd' end)p4
from table order by id
select id,'a' problemtype from table where problemtype like '%a%'
union all
select id,'b' problemtype from table where problemtype like '%b%'
union all
select id,'c' problemtype from table where problemtype like '%c%'
union all
select id,'d' problemtype from table where problemtype like '%d%'
order by id,problemtype
“或者是这样:……”
select id ,
(case when problemtype like '%a%' then 'a' end)p1,
(case when problemtype like '%b%' then 'b' end)p2,
(case when problemtype like '%c%' then 'c' end)p3,
(case when problemtype like '%d%' then 'd' end)p4
from table order by id
select id,substr(problemtype,1,1) from "table" where substr(problemtype,1,1)<>''
union all
......
要麻烦一点也可以写一个过程来处理
这个可能确实是要写个过程来处理了。这样你的记录可能是任何不固定的,处理起来比较方便。
id
,case when instr(problemtype,'a',1,1)>0 then 'a' else ''end "typeA"
,case when instr(problemtype,'b',1,1)>0 then 'b' else ''end "typeB"
,case when instr(problemtype,'c',1,1)>0 then 'c' else ''end "typeC"
,case when instr(problemtype,'d',1,1)>0 then 'd' else ''end "typeD"
from probleTable
//你可以把所有probletype都列出来,应该不是很多吧.