现有表A
ID Name Class
-------------------
12 a 34||35||36
13 b 34||36
14 c 33||34
15 d 34||35
16 e 34
17 f 34||37
求一SQL语句，希望能够得到下列查询结果：
ID Name Class
------------------
12 a 34
12 a 35
12 a 36
13 b 34
13 b 36
14 c 33
14 c 34
15 d 34
15 d 35
16 e 34
17 f 34
18 f 37
如果能够提供多种方案更好，解决后立刻给分！
------解决方案--------------------
--建个函数，以下为引用
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split)-1
WHILE CHARINDEX(@split,@s)> 0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)[email protected], ' ')
END
INSERT @re VALUES(@s)
RETURN
END
------解决方案--------------------
create table T(ID int, Name char(1), Class varchar(50))
insert T select 12, 'a ', '34||35||36 '
union all select 13, 'b ', '34||36 '
union all select 14, 'c ' , '33||34 '
union all select 15, 'd ', '34||35 '
union all select 16, 'e ', '34 '
union all select 17, 'f ', '34||37 '
declare @sql varchar(8000)
set @sql= ' '
declare @ID int, @Name char(1), @Class varchar(50)
declare T_cursor cursor for
select ID, Name, Class from T
open T_cursor
fetch next from T_cursor into @ID, @Name, @Class
while @@fetch_status=0
begin
select @[email protected]+ ' select '+rtrim(@ID)+ ', ' ' '[email protected]+ ' ' ', '
select @[email protected]+replace(@Class, '|| ', ' union all select '+rtrim(@ID)+ ', ' ' '[email protected]+ ' ' ', ')
select @[email protected]+ ' union all '
fetch next from T_cursor into @ID, @Name, @Class
end
select @sql=left(@sql, len(@sql)-9)
exec(@sql)
close T_cursor
deallocate T_cursor
------解决方案--------------------
SQL2005方法:
建立字符串分割函数,然后用cross apply 进行关联
引用:/*
jacobsan(梅) ( ) 信誉：100 Blog 2007-1-2 15:40:14 得分: 0