求sql

[aaajedll]

strid
3304,330301,3305,
33,3305,
330302,


想实现的功能是想把这列里的数据转换成多列，多列的列数是根据,号来的
strid1 strid2 strid3
3304 330301 3305
33 3305
330302

请问这个sql要怎么写？

[百年树人]

--->测试数据
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([strid] varchar(17))
insert [tb]
select '3304,330301,3305,' union all
select '33,3305,' union all
select '330302,'
go

/**
 --功能：分割字符串，取第@i个值
 --Author:josy(百年树人)
 --参数@s:字符串
 --参数@i:取第几个值
 --参数@sign:分隔符
**/
create function [dbo].[f_col](@s varchar(100),@i int,@sign varchar(10))
returns varchar(200)
as
begin
  declare @t table(id int identity(1,1),col varchar(100));
  declare @cnt int,@rel varchar(200)
  --set @s=@s+@sign --如果结尾没有分隔符，则加上这一句
  set @cnt=datalength(@s)-datalength(replace(@s,@sign,''))
  while @cnt>0
  begin
    insert @t(col) select left(@s,charindex(@sign,@s)-1)
    set @cnt=@cnt-1
    set @s=stuff(@s,1,charindex(@sign,@s),'')
  end
  select @rel=col from @t where id=@i
  return @rel
end
go

--->查询
select 
  dbo.f_col(strid,1,',') as strid1,
  dbo.f_col(strid,2,',') as strid2,
  dbo.f_col(strid,3,',') as strid3
from tb

/**
strid1     strid2     strid3
---------- ---------- ----------
3304       330301     3305
33         3305       NULL
330302     NULL       NULL

(3 行受影响)
**/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-04-01 14:18:26
-- Verstion:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--	Jul  9 2008 14:43:34 
--	Copyright (c) 1988-2008 Microsoft Corporation
--	Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([strid] varchar(17))
insert [tb]
select '3304,330301,3305' union all
select '33,3305' union all
select '330302'
--------------开始查询--------------------------
select
   reverse(PARSENAME(replace(reverse(strid),',','.'),1)) as strid1,
   reverse(PARSENAME(replace(reverse(strid),',','.'),2)) as strid2,
   reverse(PARSENAME(replace(reverse(strid),',','.'),3)) as strid3
from
   tb
----------------结果----------------------------
/* strid1                                                                                                                           strid2                                                                                                                           strid3
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
3304                                                                                                                             330301                                                                                                                           3305
33                                                                                                                               3305                                                                                                                             NULL
330302                                                                                                                           NULL                                                                                                                             NULL

(3 行受影响)

*/

[AcHerat]

--麻烦点吧！

create table tb(ar varchar(20))
insert into tb
select 'a,b,c,' union all
select 'aa,bb,' union all
select 'cc,'
go

declare @sql varchar(max)
select identity(int,1,1) as px,ar into #tb
from tb

select *,rn = row_number() over (partition by px order by getdate())
	into #tp
from(
	select distinct a.px,substring(left(a.ar,len(a.ar)-1),b.number,charindex(',',a.ar,b.number) - b.number) ar
	from #tb a,master..spt_values b
	where b.[type] = 'p' and b.number between 1 and len(a.ar) 
		and substring(',' + stuff(a.ar,len(a.ar),1,''),b.number,1) = ',')t

set @sql = 'select px'
select @sql = @sql + ',max(case rn when ' + ltrim(rn) + ' then ar else '''' end)[strid' + ltrim(rn) + ']'
from(select distinct rn from #tp)t
select @sql = @sql + ' from #tp group by px'
exec(@sql)

drop table tb,#tb,#tp


/*

px          strid1               strid2               strid3
----------- -------------------- -------------------- --------------------
1           a                    b                    c
2           aa                   bb                   
3           cc                                        

(3 行受影响)

[aaajedll]

1#2# 2位高手，你们的列是固定的吧，列是根据,号，自动增加的
5#的高手，牛

[aaajedll]

但是5#的，你好，你的好像在sql2000里用不了

[百年树人]

[Quote=引用 7 楼 aaajedll 的回复:]

但是5#的，你好，你的好像在sql2000里用不了
[/Quote]

把varchar(max) 改为 varchar(8000) 试试

[AcHerat]

--sql 2000

create table tb(ar varchar(20))
insert into tb
select 'a,b,c,' union all
select 'aa,bb,' union all
select 'cc,'
go

declare @sql varchar(8000)
select identity(int,1,1) as px,ar into #tb
from tb

select distinct a.px,substring(left(a.ar,len(a.ar)-1),b.number,charindex(',',a.ar,b.number) - b.number) ar,id = identity(int,1,1)
	into #t1
from #tb a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.ar) 
     and substring(',' + stuff(a.ar,len(a.ar),1,''),b.number,1) = ','

select px,ar,rn = (select count(*) from #t1 where px = t.px and id <= t.id)
	into #tp
from #t1 t

set @sql = 'select px'
select @sql = @sql + ',max(case rn when ' + ltrim(rn) + ' then ar else '''' end)[strid' + ltrim(rn) + ']'
from(select distinct rn from #tp)t
select @sql = @sql + ' from #tp group by px'
exec(@sql)

drop table tb,#tb,#tp,#t1

/*


px          strid1               strid2               strid3
----------- -------------------- -------------------- --------------------
1           a                    b                    c
2           aa                   bb                   
3           cc                                        

(3 行受影响)