我有这样一张表：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 再替换掉，怎么写？
------解决方案--------------------
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)
*/