sql 方法（长期更新中）

分拆列值

原著：邹建 改编：爱新觉罗.毓华  2007-12-16  广东深圳

有表tb, 如下: id          value ----------- ----------- 1           aa,bb 2           aaa,bbb,ccc 欲按id,分拆value列, 分拆后结果如下: id          value ----------- -------- 1           aa 1           bb 2           aaa 2           bbb 2           ccc

1. 旧的解决方法(sql server 2000) SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b

SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id) FROM tb A, # B WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','

DROP TABLE #

2. 新的解决方法(sql server 2005)

create table tb(id int,value varchar(30)) insert into tb values(1,'aa,bb') insert into tb values(2,'aaa,bbb,ccc') go SELECT A.id, B.value FROM(     SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb )A OUTER APPLY(     SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v) )B

DROP TABLE tb

/* id          value ----------- ------------------------------ 1           aa 1           bb 2           aaa 2           bbb 2           ccc

(5 行受影响) */

 

 

 

表 product

id Pname Pcolor 1  p1    1,2,3 2  p2    2,3 3  p3    1,2,3 4  p4    1,2

现在想得到这么一个结果

colornum  colorId

3  1 4  2 3  3

 

 

create table product (id int,Pname varchar(2),Pcolor varchar(200)) insert into product select 1,'p1','1,2,3' union all select 2,'p2','2,3' union all select 3,'p3','1,2,3' union all select 4,'p4','1,2'

--查询 declare @s varchar(max) set @s='' select @s=@s+Pcolor+',' from product if len(@s)>0 set @s=left(@s,len(@s)-1) select count(*)as colornum ,a as colorId  from f_split(@s,',') group by a order by a

/* ------------------- 3    1 4    2 3    3 */

--送你split函数 create function f_split(@str varchar(8000),@strseprate varchar(1))        returns @temp table(id int identity(1,1),a varchar(100))        as         begin            declare @i int            set @str=rtrim(ltrim(@str))            set @i=charindex(@strseprate,@str)            while @i>=1            begin                insert @temp values(left(@str,@i-1))                set @str=substring(@str,@i+1,len(@str)-@i)      --当然,这里您也可以改写为stuff,可以自己试着改写一下                set @i=charindex(@strseprate,@str)            end            if @str<>'/'               insert @temp values(@str)            return         end