一个有趣的问题，如何将1行变成多列[行列转换]

测试数据如下：
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('猪肉','维他命B1、B2、烟酸、蛋白质','疲劳、神经过敏、咳嗽、体质虚弱');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('牛肉','蛋白质、脂肪、维他命B1、B2、氨基酸、铁质','虚弱体质、贫血、手脚冰冷');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('鸡肉','蛋白质、维他命A、B2、B1、烟酸','消化不良、恢复体力、癌症');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('鸡翼','维他命A、成胶原','强化血管、有利皮肤');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('鸡胸肉','蛋白质、不饱和脂肪酸','防口腔炎、胃炎');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('鸡汤','','消除疲劳、病后恢复体力、改善发质');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('鸡蛋','维他命A、B2、D、铁质、蛋白质','皮肤问题、心脏病、糖尿病、老化');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('猪肚','维他命A、B12、铁质','贫血、强肝');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('猪腿','蛋白质、维他命B','防血栓、有利皮肤');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('羊肉','维他命B1、B2、E、铁质','手脚冰冷、滋补强健、贫血');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('牛蒡','钙质、纤维、磷、维他命C','抗癌、治便秘、利尿');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('秋刀鱼','维他命D、烟酸、脂肪、蛋白质、维他命A','贫血、眼睛疲劳、癌症、骨骼疏松症、高血压');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('沙甸鱼','维他命B2、D、脂肪、蛋白质、钙','中风、骨骼疏松症、动脉硬化');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('鳗鱼','维他命A、B1、B2、D、E','');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('鲑鱼','维他命B2、D、E、蛋白质、脂肪','利尿、体力衰退');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('鳕鱼','蛋白质、维他命D、A、B2、钙','贫血、水肿、视力减退、感冒');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('乌贼','蛋白质、钾','中风、糖尿病、癌症');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('蚬','铁、维他命B2、B12、钙','贫血、肝病、骨骼疏松病、宿醉');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('蛤仔','含丰富铁质、维他命B12、蛋白质','肝病、贫血、浮肿');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('扇贝','维他命B2、E、蛋白质','头痛、晕眩、肩膀酸痛');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('牡蛎','维他命B2、氨基酸、铁质','抑制胆固醇、高血压、贫血');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('章鱼','蛋白质、氨基酸','降胆固醇、维持血压正常');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('鲍鱼','蛋白质、脂肪、钙','滋阴清热、养肝明目');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('海苔','钙、叶绿素、维他命A、B、矿物质','降胆固醇');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('芝士','钙、脂肪、蛋白质、维他命A、B2','骨骼疏松症、便秘、压力、口角炎');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('酸奶酪','钙、维他命A、B2、蛋白质、脂肪','便秘、腹泻、老化、癌症、骨骼疏松症');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('提子干','维他命C、钙、钾、镁、磷','心藏病、中风、骨骼疏松症');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('士多啤梨','维他命C','肝斑、雀斑、皮肤问题、感冒、牙周病');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('芒果','维他命A、C、E','防止视力衰退、保护眼睛、抗氧化、滋润肌肤防止视力衰退、保护眼睛、抗氧化、滋润肌肤');
insert into SWYY (C_SW,C_YYZB,C_YFJB) values('山竹','叶酸','防止贫血');
commit;

如果要查询哪些食品含有B2，哪些食品可以预防治疗贫血，这很容易实现。
但现在我想实现的是，创建两个视图，一个以营养指标为关键字，列出所有的营养指标以及含有此营养物质的食物
另一个视图类似，以能治疗预防的疾病为关键字，列出所有的可预防治疗的疾病以及能预防治疗此疾病的食物

营养指标列表和可预防治疗疾病列表都以顿号(、)为分隔符

请问这两个视图的脚本应该怎么写？
ps：这能否通过一条sql语句来实现？

解决方法之一

首先看看营养指标和可预防治疗疾病中最多都包含多少项
SQL> select max(length(c_yyzb)-length(replace(c_yyzb,'、'))) from swyy;

MAX(LENGTH(C_YYZB)-LENGTH(REPLACE(C_YYZB,'、')))
------------------------------------------------
9

SQL> select max(length(c_yfjb)-length(replace(c_yfjb,'、'))) from swyy;

MAX(LENGTH(C_YFJB)-LENGTH(REPLACE(C_YFJB,'、')))
------------------------------------------------
7

然后创建分隔函数
CREATE OR REPLACE FUNCTION GetItem(val in varchar2,idx in number,separator in varchar2 default '、') RETURN varchar2 IS
item varchar2(20);
pos number;
pos2 number;
BEGIN
item := '';
if val is null then
item := null;
end if;
if idx > 1 then
pos := instr(val,separator,1,idx-1);
pos2 := instr(val,separator,1,idx);
if pos=0 and pos2=0 then
item:= null;
else
if pos2<>0 then
item:= substr(val,pos+length(separator),pos2-pos-length(separator));
else
item:=substr(val,pos+length(separator));
end if;
end if;
else
pos := instr(val,separator,1,idx);
if pos=0 then
item:=val;
else
item:= substr(val,1,pos-1);
end if;
end if;
RETURN item;
END GetItem;
/
创建获得每种食物所包含的营养物质或者可预防治疗疾病的视图
create or replace view swyy_yyzb as
select * from (
select C_SW,getitem(C_YYZB,1) C_YYYS from swyy
union
select C_SW,getitem(C_YYZB,2) C_YYYS from swyy
union
select C_SW,getitem(C_YYZB,3) C_YYYS from swyy
union
select C_SW,getitem(C_YYZB,4) C_YYYS from swyy
union
select C_SW,getitem(C_YYZB,5) C_YYYS from swyy
union
select C_SW,getitem(C_YYZB,6) C_YYYS from swyy
union
select C_SW,getitem(C_YYZB,7) C_YYYS from swyy
union
select C_SW,getitem(C_YYZB,8) C_YYYS from swyy
union
select C_SW,getitem(C_YYZB,9) C_YYYS from swyy
union
select C_SW,getitem(C_YYZB,10) C_YYYS from swyy
)
where c_yyys is not null
;

为了便于统计，将数据中的维他命和维生素统统去掉
update swyy set c_yyzb = replace(replace(c_yyzb,'维他命'),'维生素');
commit;



create or replace view swyy_yfjb as
select * from (
select C_SW,getitem(C_YFJB,1) C_YFJB from swyy
union
select C_SW,getitem(C_YFJB,2) C_YFJB from swyy
union
select C_SW,getitem(C_YFJB,3) C_YFJB from swyy
union
select C_SW,getitem(C_YFJB,4) C_YFJB from swyy
union
select C_SW,getitem(C_YFJB,5) C_YFJB from swyy
union
select C_SW,getitem(C_YFJB,6) C_YFJB from swyy
union
select C_SW,getitem(C_YFJB,7) C_YFJB from swyy
union
select C_SW,getitem(C_YFJB,8) C_YFJB from swyy
)
where C_YFJB is not null
;

最后创建最终视图：
哪些食物包含此营养物质的视图
create or replace view swyyzb as
select c_yyys,substr(col_list,1-length(col_list)) C_SW from
(
select c_yyys,max(sys_connect_by_path(c_sw,'、')) col_list from
(
select c_yyys,c_sw,(row_number() over(order by c_yyys,c_sw desc) + rank() over(order by c_yyys)) rn
from swyy_yyzb
)
connect by rn-1 = prior rn
group by c_yyys
)
/
哪些食物可预防治疗此疾病的视图
create or replace view swyfjb as
select c_yfjb,substr(col_list,1-length(col_list)) C_SW from
(
select c_yfjb,max(sys_connect_by_path(c_sw,'、')) col_list from
(
select c_yfjb,c_sw,(row_number() over(order by c_yfjb,c_sw desc) + rank() over(order by c_yfjb)) rn
from swyy_yfjb
)
connect by rn-1 = prior rn
group by c_yfjb
)
/

在oracle9201下测试通过，8i不支持sys_connect_by_path，做起来会很麻烦。
一些地方可以优化的，不去做了，比如union改成union all应该会更好一些
select C_SW,getitem(C_YYZB,6) C_YYYS from swyy后面完全可以加上条件判断语句 where instr(C_YYZB,'、',1,5)>0
来过滤掉不需要处理的记录

我这方法感觉很麻烦。谁还有更好的方法，请不吝赐教

解决方法之二（用管道函数）

create or replace type TypeSumVarchar as object
(
sum VARCHAR2(400),
static function ODCIAggregateInitialize(sctx IN OUT TypeSumVarchar)
return number,
member function ODCIAggregateIterate(self IN OUT TypeSumVarchar,
value IN varchar2) return number,
member function ODCIAggregateTerminate(self IN TypeSumVarchar,
returnValue OUT VARCHAR2, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT TypeSumVarchar,
ctx2 IN TypeSumVarchar) return number
);
/
CREATE OR REPLACE TYPE BODY TYPESUMVARCHAR is
static function ODCIAggregateInitialize(sctx IN OUT TypeSumVarchar)
return number is
begin
sctx := TypeSumVarchar('');
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT TypeSumVarchar, value IN VARCHAR2)
return number is
begin
self.sum:=self.sum||'、'||value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN TypeSumVarchar, returnValue OUT
VARCHAR2, flags IN number) return number is
begin
returnValue := substr(self.sum,2);
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT TypeSumVarchar, ctx2 IN
TypeSumVarchar) return number is
begin
return ODCIConst.Success;
end;
end;
/


CREATE OR REPLACE FUNCTION SUMC (input VARCHAR2) RETURN VARCHAR2
AGGREGATE USING TypeSumVarchar;
/

drop type MyTypeList
/

create or replace type MyType as object (
f1 varchar2(80),
f2 varchar2(80)
);
/

create type MyTypeList as table of MyType;
/

CREATE OR REPLACE function PipelineT
return MyTypeList pipelined as
v_MyType MyType;
cursor cs is select * from swyy where c_yyzb is not null;
v_str varchar2(100);
v_idx binary_integer;
begin
for c in cs loop
v_str := c.c_yyzb;
loop
v_idx := instr(v_str,'、');
exit when v_idx = 0;
v_MyType := MyType(c.c_sw,substr(v_str,1,v_idx-1));
pipe row(v_MyType);
v_str := substr(v_str,v_idx+1);
end loop;
v_MyType := MyType(c.c_sw,v_str);
pipe row(v_MyType);
end loop;
return;
end PipelineT;
/

CREATE OR REPLACE function PipelineS
return MyTypeList pipelined as
v_MyType MyType;
cursor cs is select * from swyy where c_yfjb is not null;
v_str varchar2(100);
v_idx binary_integer;
begin
for c in cs loop
v_str := c.c_yfjb;
loop
v_idx := instr(v_str,'、');
exit when v_idx = 0;
v_MyType := MyType(c.c_sw,substr(v_str,1,v_idx-1));
pipe row(v_MyType);
v_str := substr(v_str,v_idx+1);
end loop;
v_MyType := MyType(c.c_sw,v_str);
pipe row(v_MyType);
end loop;
return;
end PipelineS;
/

然后用select f2,sumc(f1) f1 from table(pipelinet) group by f2;
获得营养指标和食物的对应关系
select f2,sumc(f1) f1 from table(pipelines) group by f2;
获得可预防治疗疾病和食物的对应关系

SQL> create view swyyzb_type as select f2,sumc(f1) f1 from table(pipelinet) grou
p by f2;

视图已建立。

SQL> create view swyfjb_type as select f2,sumc(f1) f1 from table(pipelines) grou
p by f2;

视图已建立。

-- 得到在INSTRING字符串中的分割符INPATTERN的个数
CREATE OR REPLACE FUNCTION NUM_CHARS(INSTRING VARCHAR2, INPATTERN VARCHAR2)
RETURN NUMBER
IS
COUNTER NUMBER;
NEXT_INDEX NUMBER;
STRING VARCHAR2(2000);
PATTERN VARCHAR2(2000);
BEGIN
COUNTER := 0;
IF INSTRING IS NULL THEN
GOTO FANHUI ;
END IF;
NEXT_INDEX := 1;
STRING := LOWER(INSTRING);
PATTERN := LOWER(INPATTERN);
FOR I IN 1 .. LENGTH(STRING) LOOP
IF (LENGTH(PATTERN) 1 loop
DEL_INDEX := INSTR(STRING,'、');
STRING := SUBSTR(STRING,DEL_INDEX + 1,length(STRING));
N_INDEX := N_INDEX -1;
end loop;
DEL_INDEX := INSTR(STRING,'、');
STRING := SUBSTR(STRING,1,DEL_INDEX - 1);
RETURN STRING;
END;
/

select a.c_sw,a.c_yyzb,b.r,ST_CHARS(a.c_yyzb,b.r)
from
(select c_sw, c_yyzb,num_chars(c_yyzb,'、') as tot from swyy) a,
(select r from (select rownum r from dual connect by rownum<120 b where>

谢西门吹牛兄指教

一句SQL倒是能搞定，就是执行效率太慢。。。。。不知道该怎么优化才好。

select decode(sign (instr(nvl(str,''), '、')),0,(decode(idx,1,nvl(str,''),'')),
decode (sign( LENGTH(str)-LENGTH(REPLACE(str,'、')) + 1 - idx),
1, decode(idx,1,substr(str,1,INSTR (str, '、', 1, 1) - length('、')) ,substr(str,INSTR (str,'、', 1, idx - 1) + LENGTH ('、'), INSTR (str, '、', 1, idx) - INSTR (str, '、', 1, idx - 1) - LENGTH ('、'))),
0, nvl(SUBSTR (str,INSTR (str, '、', 1, idx-1)+LENGTH ('、')),''),
-1, ''))
AS result,c_sw
from
( select c_sw ,
c_yyzb, --as str
c_yfjb as str,
j,level as idx
from ( select swyy.*,length(c_yfjb)-length(replace(c_yfjb,'、'))as j from swyy ) A
connect by level

==好像回复有字符数目限制，接上文

connect by level

呵呵，原来不让发小于号

select decode(sign (instr(nvl(str,''), '、')),0,(decode(idx,1,nvl(str,''),'')),
decode (sign( LENGTH(str)-LENGTH(REPLACE(str,'、')) + 1 - idx),
1, decode(idx,1,substr(str,1,INSTR (str, '、', 1, 1) - length('、')) ,substr(str,INSTR (str,'、', 1, idx - 1) + LENGTH ('、'), INSTR (str, '、', 1, idx) - INSTR (str, '、', 1, idx - 1) - LENGTH ('、'))),
0, nvl(SUBSTR (str,INSTR (str, '、', 1, idx-1)+LENGTH ('、')),''),
-1, ''))
AS result,c_sw
from
( select c_sw ,
c_yyzb, --as str
c_yfjb as str,
j,level as idx
from ( select swyy.*,length(c_yfjb)-length(replace(c_yfjb,'、'))as j from swyy ) A
connect by level〈=j
group by c_sw,c_yyzb,c_yfjb,j,level
order by c_sw,c_yyzb,c_yfjb,j,level
)