求解sql

Question

表格式如下:
id str
1  A;B
2  C;F;SS
3  张三;小三
…要变成:
id str
1  A
1  B
2  C
2  F
2  SS
3  张三
3  小三
…str内容是用”;”号连接,个数和长度都是不固定的
求解?

Answer 1

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-01-31 15:22:48
-- Version:
--      Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) 
-- Jun 17 2011 00:57:23 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([id] int,[str] varchar(9))
insert [huang]
select 1,'A;B' union all
select 2,'C;F;SS' union all
select 3,'张三;小三'
--------------开始查询--------------------------
select
    id, 
    SUBSTRING([str],number,CHARINDEX(';',[str]+';',number)-number) as [str] 
from
    [huang] a,master..spt_values 
where
    number >=1 and number<len([str])  
    and type='p'
    and substring(';'+[str],number,1)=';'----------------结果----------------------------
/* 
id          str
----------- ---------
1           A
2           C
2           F
2           SS
3           张三
3           小三(6 行受影响)
*/

Answer 2

SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b     
SELECT A.id, SUBSTRING(A.str, B.id, CHARINDEX(';', A.str + ';', B.id) - B.id) FROM tb A, # B WHERE SUBSTRING(';' + A.str, B.id, 1) = ';'  
DROP TABLE # 

Answer 3

修改1樓的。select
    id, 
    SUBSTRING([str],number,CHARINDEX(';',[str]+';',number+1)-1) as [str] 
from
    [huang] a,master..spt_values 
where
    number >=1 
    --and number<=len([str])  
    and type='p'
    and substring(';'+[str],number,1)=';'

Answer 4

--创建临时表
create table #temp
(
intPK int primary key identity(1,1),
id int,
str varchar(100)
)
--创建临时表
create table #temp2
(
id int ,
str varchar(100)
)
--数据插入临时表
--TestSplit为楼主使用表名
insert into #temp(id,str) select id,[str] from TestSplitdeclare @count int,--记录数
@index int,--索引
@id int,--表内容
@str varchar(100),--表内容
@charIndex int,--字符索性位置
@len int,--截取字符串长度
@splitCont varchar(100),--截取字符内容
@splitChar varchar(1)--截取字符
set @index=1
set @charIndex=1
set @splitCont=''
set @splitChar=';'
select @count=count(*) from #temp
------------------循环表内容 start------------------
while @index<=@count
begin
select @id=id from #temp where intPK=@index--取得表id内容
select @str=[str] from #temp where intPK=@index--取得表str内容
select @charIndex=charindex(@splitChar,@str)
while @charIndex>0
begin
select @splitCont=substring(@str,1,@charIndex-1)
insert into #temp2(id,str) values(@id,@splitCont)
select @str = substring(@str,@charIndex+1,len(@str)-@charIndex)
select @charIndex=charindex(@splitChar,@str)
end
insert into #temp2(id,str) values(@id,@str)
set @index=@index+1
end
------------------循环表内容 end------------------
select * from #temp2
drop table #temp
drop table #temp2