求解sql
表格式如下:
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内容是用”;”号连接,个数和长度都是不固定的
求解?
[解决办法]
----------------------------
-- 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 行受影响)
*/
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 #
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)=';'
[解决办法]
--创建临时表
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 TestSplit
declare @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
