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