分拆列值
原著:邹建 改编:爱新觉罗.毓华 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