求一条sql

Question

表A
Name         school
AA,BB,CC     上海1,上海2  
DD           上海2 
EE           上海3 
FF           上海4,上海7
TT,SS        上海5 
AA,YY,CC     上海6 得到数据Name         school
AA 上海1
AA 上海2  
BB 上海1
BB 上海2  
CC 上海1
CC  上海2  
DD           上海2 
EE           上海3 
FF           上海4
FF           上海7
TT 上海5 
SS 上海5 
AA 上海6 
YY 上海6 
CC 上海6 谢谢！

Answer 1

分拆列值 原著：邹建 
改编：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-16  广东深圳 有表tb, 如下: 
id          value 
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 
欲按id,分拆value列, 分拆后结果如下: 
id          value 
----------- -------- 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc 1. 旧的解决方法(sql server 2000) 
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id) 
FROM tb A, # B 
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ',' DROP TABLE # 2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30)) 
insert into tb values(1,'aa,bb') 
insert into tb values(2,'aaa,bbb,ccc') 
go 
SELECT A.id, B.value 
FROM( 
    SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLACE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb 
)A 
OUTER APPLY( 
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v) 
)B DROP TABLE tb /* 
id          value 
----------- ------------------------------ 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc (5 行受影响) 
*/

Answer 2

有表tb, 如下: 
id          value 
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 
欲按id,分拆value列, 分拆后结果如下: 
id          value 
----------- -------- 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc 1. 旧的解决方法(sql server 2000) 
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id) 
FROM tb A, # B 
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ',' DROP TABLE # 2. 新的解决方法(sql server 2005) create table tb(id int,value varchar(30)) 
insert into tb values(1,'aa,bb') 
insert into tb values(2,'aaa,bbb,ccc') 
go 
SELECT A.id, B.value 
FROM( 
    SELECT id, [value] = CONVERT(xml,' <root> <v>' + REPLACE([value], ',', ' </v> <v>') + ' </v> </root>') FROM tb 
)A 
OUTER APPLY( 
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v) 
)B DROP TABLE tb /* 
id          value 
----------- ------------------------------ 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc (5 行受影响) 
*/

Answer 3

if object_id('tb') is not null  
  drop table tb
gocreate table tb(name varchar(100),school varchar(100))
goinsert into tb
select 'AA,BB,CC',    '上海1,上海2' union all  
select 'DD',          '上海2' union all 
select 'EE',          '上海3' union all 
select 'FF',          '上海4,上海7' union all 
select 'TT,SS',       '上海5' union all 
select 'AA,YY,CC',    '上海6' select a.name, substring(a.school,number,charindex(',',a.school+',',number)-number) as school 
from
(
select  substring(a.name,number,charindex(',',a.name+',',number)-number) as name,school
from tb a ,master..spt_values b 
where b.type = 'P' and substring(','+a.name,number,1)=','
)a,master..spt_values b 
where b.type = 'P' and substring(','+a.school,number,1)=','drop table tb

Answer 4

你的substring为什么有四个参数？Number哪来的？