怎样消除字符串中的重复项？

[3tzjq]

我有这样一张表：MaterialInfo
SerialId MaterialId SubVendor SubPartNumber Manufacturer EnabledMRPCal Priority...
1 TEST01 VR001 PN001 AIX 1 1
2 TEST01 VR001 PN002 AIX 1 2
3 TEST01 VR002 PN100 PHI 1 3
4 TEST02 VW001 PN200 SPLC 1 1
5 TEST02 VW001 PN201 1 2
6 TEST02 VW001 PN202 1 3
7 TEST02 VW001 PN301 1 4
...
上面的数据乱了，请看下图：

我希望得到下面这样的结果(将每个Material的SubVendor，SubPartNumber，Manufacturer 组成一个字符串)：
[MaterialInfo]
AIX 'PN001' or 'PN002', PHI 'PN100'
SPLC 'PN200', VW001 'PN201' or 'PN202' or 'PN301'

注释：
如果 Manufacturer 为空，则前缀用 SubVendor 代替，后面分别用逗号','分开；
如果 Manufacturer 或 SubVendor 是相同的，则只在第一个 SubPartNumber 前加上此前缀，后面用 'or' 串联；
输出字符串的顺序应根据[Priority]进行排序。

我现在写了这样一个合并列数据的函数：

CREATE FUNCTION dbo.GetMaterialInfo(@MaterialId varchar(50))RETURNS varchar(300)
AS
BEGIN
  Declare @Result varchar(300);
  Select @Result = '';
  Select @Result = @Result+', '+RTRIM(CASE WHEN Manufacturer IS NOT NULL THEN Manufacturer ELSE SubVendor END) + ' ''' + RTRIM(SubPartNumber) + '''' from dbo.MaterialInfo WHERE MaterialId = @MaterialId ORDER BY [Priority] ASC;
  Select @Result = CASE WHEN LEN(@Result) > 0 THEN STUFF(@Result,1,1,'') ELSE @Result END;
  Return @Result;
END
GO

但得到的结果是这样的：
AIX 'PN001', AIX 'PN002', PHI 'PN100'
SPLC 'PN200', VW001 'PN201', VW001 'PN202', VW001 'PN301'

也就是说还需要将重复的 Manufacturer 或 SubVendor 再替换掉，怎么写？

[3tzjq]

[3tzjq]

在线等，给大伙儿拜年了哈！

[唐诗三百首]

try this,

create table MaterialInfo
(SerialId int,MaterialId varchar(10),SubVendor varchar(10),SubPartNumber varchar(10),
 Manufacturer varchar(10),EnabledMRPCal int,[Priority] int)

insert into MaterialInfo
 select 1,'TEST01','VR001','PN001','AIX',1,1 union all
 select 2,'TEST01','VR001','PN002','AIX',1,2 union all
 select 3,'TEST01','VR002','PN100','PHI',1,3 union all
 select 4,'TEST02','VW001','PN200','SPLC',1,1 union all
 select 5,'TEST02','VW001','PN201','',1,2 union all
 select 6,'TEST02','VW001','PN202','',1,3 union all
 select 7,'TEST02','VW001','PN301','',1,4


-- 建函数
create function dbo.GetMaterialInfo(@MaterialId varchar(50))
returns varchar(300)
as
begin
 declare @r varchar(300);
 
 with t as
 (select case when Manufacturer<>'' then Manufacturer 
              else SubVendor end 'Manufacturer',
         SubPartNumber
  from MaterialInfo
  where MaterialId=@MaterialId)
 select @r=isnull(@r+', ','')
          +a.Manufacturer+' '
          +stuff((select ''' or '''+b.SubPartNumber 
                  from t b 
                  where b.Manufacturer=a.Manufacturer
                  for xml path('')),1,5,'')
          +''''
  from t a
  group by a.Manufacturer
 
 return @r
end


select MaterialId,
       dbo.GetMaterialInfo(MaterialId) 'MaterialInfo'
 from MaterialInfo
 group by MaterialId

/*
MaterialId MaterialInfo
---------- ------------------------------------------------------
TEST01     AIX 'PN001' or 'PN002', PHI 'PN100'
TEST02     SPLC 'PN200', VW001 'PN201' or 'PN202' or 'PN301'

(2 row(s) affected)
*/

[jorkin_me]

with功能的确强大。

[lis_mode]

create table MaterialInfo
(SerialId int,MaterialId varchar(10),SubVendor varchar(10),SubPartNumber varchar(10),
 Manufacturer varchar(10),EnabledMRPCal int,[Priority] int)
 
insert into MaterialInfo
 select 1,'TEST01','VR001','PN001','AIX',1,1 union all
 select 2,'TEST01','VR001','PN002','AIX',1,2 union all
 select 3,'TEST01','VR002','PN100','PHI',1,3 union all
 select 4,'TEST02','VW001','PN200','SPLC',1,1 union all
 select 5,'TEST02','VW001','PN201','',1,2 union all
 select 6,'TEST02','VW001','PN202','',1,3 union all
 select 7,'TEST02','VW001','PN301','',1,4
 

 with t as 
 (
	 select *,
	  a.c1 +  stuff( ( select ' or ''' + b.SubPartNumber + '''' 
			   from MaterialInfo as b 
			   where a.MaterialId = b.MaterialId 
				 and a.SubVendor = b.SubVendor 
				 and a.Manufacturer = b.Manufacturer 
			   for xml path( '' ) ), 1, 4, '' ) as c2
	 from (
 		 select distinct
		   MaterialId
		 , SubVendor
		 , Manufacturer
		 , case when Manufacturer = '' then SubVendor + ' ' else Manufacturer + ' ' end c1
		 from MaterialInfo 
	 ) a
  )
  
  select
    stuff( ( select ',' + c2 from t where t.MaterialId = MaterialInfo.MaterialId for xml path( '' ) ), 1, 1, '' ) as c3
  from MaterialInfo
  group by MaterialId
  

[3tzjq]

谢谢！我去试试

[3tzjq]

貌似不能按 [Priority] 字段排序，学习下先

[3tzjq]

改成:

create function dbo.GetMaterialInfo(@MaterialId varchar(50))
returns varchar(300)
as
begin
 declare @r varchar(300);
 
 with t as
 (select top(50) case when Manufacturer<>'' then Manufacturer 
              else SubVendor end 'Manufacturer',
         SubPartNumber
  from MaterialInfo
  where MaterialId=@MaterialId
  order by [Priority] ASC)
 select @r=isnull(@r+', ','')
          +a.Manufacturer+' '
          +stuff((select ''' or '''+b.SubPartNumber 
                  from t b 
                  where b.Manufacturer=a.Manufacturer
                  for xml path('')),1,5,'')
          +''''
  from t a
  group by a.Manufacturer
  
  return @r
   
end