想了半天不懂得怎么写，上网求助。求一SELECT语句

[awuZhang]

select * from teach_class

CC_ID TC_VERSION SPECIALITY_LIST MAJOR_LIST ......
373 200420051 200201107,200201107 2002011071,2002011072 ......

有没有什么办法能显示成两条？（结果如下）

CC_ID TC_VERSION SPECIALITY_LIST MAJOR_LIST ......
373 200420051 200201107 2002011071 ......
373 200420051 200201107 2002011072 ......

如果有N个“,”就显示成N条。偶想了半天没有头绪只好上来向高手求教

(要不是 select * from teach_class ... 后面还有 join 别的表，我就直接在程序内分割了)

[long_205]

select * from teach_class
union all
select * from teach_class

[mahf]

学习!

[lsxaa]

select * from teach_class
union all
select * from teach_class

[zjcxc]

SPECIALITY_LIST MAJOR_LIST
这两列的,个数是相同的么? 如果不相同,怎么显示?

CC_ID TC_VERSION 是表中的主键么?

[awuZhang]

回　zjcxc(邹建)
SPECIALITY_LIST，MAJOR_LIST　两列的个数是固定相同的　CC_ID是主建

[lsxaa]

没仔细看，我写的不对

[lsxaa]

写一个临时表,来分割

[NinGoo]

Mark

[zjcxc]

--生成处理临时表
set rowcount 8000
select id=identity(int) into #t
from syscolumns a,syscolumns b
set rowcount 0

--拆分查询
select a.*,b.MAJOR_LIST
from(
select CC_ID,TC_VERSION
,SPECIALITY_LIST=substring(SPECIALITY_LIST,b.id,charindex(',',SPECIALITY_LIST+',',b.id)-b.id)
from teach_class a,#t b
where substring(','+SPECIALITY_LIST,b.id,1)=','
)a,(
select CC_ID
,MAJOR_LIST=substring(MAJOR_LIST,b.id,charindex(',',MAJOR_LIST+',',b.id)-b.id)
from teach_class a,#t b
where substring(','+MAJOR_LIST,b.id,1)=','
)b where a.CC_ID=b.CC_ID

[chinaandys]

学习。。。

[ybiori]

高！