一个比较棘手的sql语句问题

Question

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,name varchar(11))
insert into #T
select 1,'xx xxx xxxx'--> 数字辅助表法——数字辅助表的最大值等于待拆分字段的最大字符个数就足够用了：
if object_id('tempdb.dbo.#Nums') is not null drop table #Nums
select top 11 num=identity(int,1,1) into #Nums from syscolumns,sysobjects
select a.id,name=substring(a.name+' ',b.num,charindex(' ',a.name+' ',b.num+1)-b.num) from #T a, #Nums b where substring(' '+a.name,b.num,1)=' '
/*
id       name
-------- --------
1        xx
1        xxx
1        xxxx
*/
;
--> CTE递归法——通过递归找出各个分隔字符串的位置，效率比数字表法略高：
WITH Pos (id,P1,P2) AS
(
select id,charindex(' ',' '+name),charindex(' ',name+' ')+1 from #T
union all
select a.id,b.P2,charindex(' ',name+' ',b.P2)+1 from #T a join Pos b on a.id=b.id where charindex(' ',name+' ',b.P2)>0
)
select a.id,name=substring(a.name+' ',b.P1,b.P2-b.P1-1) from #T a join Pos b on a.id=b.id order by a.id option (maxrecursion 0)
/*
id       name
-------- --------
1        xx
1        xxx
1        xxxx
*/--> 删除测试：
drop table #T,#Nums

Answer 1

楼上的厉害不过我描述的不清楚。那个xx，xxx，xxxx是未知的。也就是说是不同的数据。具体就是这个字段里面是人名。有的是3个人名，有的是4个。我的想法是把他们拆出来一个人对应一条记录。

Answer 2

 
/******************************************************************************/
/*回复：20080526003总：00050                                                   */
/*主题：列拆分成行                                                                        */
/*作者：二等草                                                                            */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
create table tb(id int,name varchar(20))
insert tb select 1,'xx xxx xxxx'
go
--代码--------------------------------------------------------------------------
create function gett(@id int)
returns @t table(id int identity,name varchar(10))
as
begin
 declare @s varchar(1000),@i int,@j int
 select @i = 1,@j = 1
 select @s = rtrim(name) from tb where id = @id
 if isnull(@s,'')='' return
 while charindex(' ',@s,@i)>0 
 begin
   set @j = charindex(' ',@s,@i)
   insert @t(name) select substring(@s,@i,@j-@i)
   set @i = @j + 1
 end
 insert @t(name) select substring(@s,@i,len(@s))
 return
end
go
 select * from dbo.gett(1)
go
/*结果--------------------------------------------------------------------------
id          name       
----------- ---------- 
1           xx
2           xxx
3           xxxx--清除------------------------------------------------------------------------*/
drop function gett
go
drop table tb

Answer 3

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,name varchar(100))
insert into #T
select 1,'xx xxx xxxx' union all
select 2, 'a b c d e f'
--> 数字辅助表法——数字辅助表的最大值等于待拆分字段的最大字符个数就足够用了：
if object_id('tempdb.dbo.#Nums') is not null drop table #Nums
select top 11 num=identity(int,1,1) into #Nums from syscolumns,sysobjects
select a.id,name=substring(a.name+' ',b.num,charindex(' ',a.name+' ',b.num+1)-b.num) from #T a, #Nums b where substring(' '+a.name,b.num,1)=' '
/*
id       name
-------- --------
1        xx
1        xxx
1        xxxx
2        a
2        b
2        c
2        d
2        e
2        f
*/
;
--> CTE递归法——通过递归找出各个分隔字符串的位置，效率比数字表法略高：
WITH Pos (id,P1,P2) AS
(
select id,charindex(' ',' '+name),charindex(' ',name+' ')+1 from #T
union all
select a.id,b.P2,charindex(' ',name+' ',b.P2)+1 from #T a join Pos b on a.id=b.id where charindex(' ',name+' ',b.P2)>0
)
select a.id,name=substring(a.name+' ',b.P1,b.P2-b.P1-1) from #T a join Pos b on a.id=b.id order by a.id option (maxrecursion 0)
/*
id       name
-------- --------
1        xx
1        xxx
1        xxxx
2        a
2        b
2        c
2        d
2        e
2        f
*/--> 删除测试：
drop table #T,#Nums

Answer 4

/******************************************************************************/
/*回复：20080526003总：00050                                                   */
/*主题：列拆分成行                                                                        */
/*作者：二等草                                                                            */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
create table tb(id int,name varchar(20))
insert tb select 1,'xx xxxwew xxxwewx'
insert tb select 2,'wewyy ewyyewy yyyewy'
insert tb select 3,'zzz ddd xxweewwxx'
insert tb select 4,'xwewx xxwex xxxxwew'
go
--代码--------------------------------------------------------------------------
declare @s varchar(8000)
select @s= isnull(@s,'')+rtrim(name) from tb 
while charindex('  ',@s)>0 set @s = replace(@s,'  ',' ')
set @s = replace(@s,' ',''' insert @t(name) select ''')
set @s = 'declare @t table(id int identity,name varchar(20)) insert @t(name) select '''+@s
set @s = @s+''' select * from @t'
exec(@s)
go
/*结果--------------------------------------------------------------------------
id          name                 
----------- -------------------- 
1           xx
2           xxxwew
3           xxxwewxwewyy
4           ewyyewy
5           yyyewyzzz
6           ddd
7           xxweewwxxxwewx
8           xxwex
9           xxxxwew--清除------------------------------------------------------------------------*/drop table tb