表partInfo
ID partType
--------------------------
pt001 A1
pt001 A1/A2
pt002 B1
pt002 B2
-------------------------
要求结果
ID partType
--------------------------
pt001 A1/A2
pt002 B1/B2
ID partType
--------------------------
pt001 A1
pt001 A1/A2
pt002 B1
pt002 B2
-------------------------
要求结果
ID partType
--------------------------
pt001 A1/A2
pt002 B1/B2
returns varchar(1000)
as
begin
declare @s varchar(1000)
select @s = isnull(@s+'/','')+case when charindex('/'+parttype+'/','/'+@s+'/')>0 then '' else parttype end from partinfo where id = @id
return @s
end
goselect id,dbo.f_str(id)
from from partinfo
group by id
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-02 16:47:45
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[partInfo]
if object_id('[partInfo]') is not null drop table [partInfo]
go
create table [partInfo]([ID] varchar(5),[partType] varchar(5))
insert [partInfo]
select 'pt001','A1' union all
select 'pt001','A1/A2' union all
select 'pt002','B1' union all
select 'pt002','B2'
--------------开始查询--------------------------
;with f as
(
Select
a.id,partType=substring(a.partType,b.number,charindex('/',a.partType+'/',b.number)-b.number)
from
partInfo a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.partType)
where
substring('/'+a.partType,b.number,1)='/'
),
f1 as
(
select distinct * from f
)
select
id, [partType]=stuff((select '/'+[partType] from f1 t where id=f1.id for xml path('')), 1, 1, '')
from
f1
group by
id ----------------结果----------------------------
/* id partType
----- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pt001 A1/A2
pt002 B1/B2(2 行受影响)
*/
If object_id('partInfo') is not null
Drop table partInfo
;
Create table partInfo(ID nvarchar(5),partType nvarchar(5))
go
Insert into partInfo
select 'pt001','A1' union all
select 'pt001','A1/A2' union all
select 'pt002','B1' union all
select 'pt002','B2'
go ;with cte
as
(select id,cast(replace((select replace(parttype,'/','*') as [data()]
from partinfo where id = a.id FOR XML PATH('r')),'*','</r><r>') as xml)as x
from partinfo a
group by id),
cte1
as
(
select distinct id,v
from cte
CROSS APPLY
(SELECT v=t.x.value('.','VARCHAR(10)') FROM cte.x.nodes('//r') AS t(x) ) b)
select id, stuff((select ','+v as [data()] from cte1 where id = a.id for xml path('')),1,1,'')
from cte1 a
group by id
id
----- --------
pt001 A1 ,A2
pt002 B1 ,B2(2 行受影响)