求sql

Question

strid
3304,330301,3305,
33,3305,
330302,
想实现的功能是想把这列里的数据转换成多列，多列的列数是根据,号来的
strid1  strid2  strid3
3304    330301  3305
33      3305
330302请问这个sql要怎么写？

Answer 1

本帖最后由 josy 于 2011-04-01 14:35:38 编辑

  

Answer 2

----------------------------------------------------------------
-- 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 行受影响)*/

Answer 3

--麻烦点吧！create table tb(ar varchar(20))
insert into tb
select 'a,b,c,' union all
select 'aa,bb,' union all
select 'cc,'
godeclare @sql varchar(max)
select identity(int,1,1) as px,ar into #tb
from tbselect *,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) = ',')tset @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 行受影响)

Answer 4

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

Answer 5

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

Answer 6

--sql 2000create table tb(ar varchar(20))
insert into tb
select 'a,b,c,' union all
select 'aa,bb,' union all
select 'cc,'
godeclare @sql varchar(8000)
select identity(int,1,1) as px,ar into #tb
from tbselect 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 tset @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 行受影响)