关于检核BOM表重复的问题。求MS SQL2000 的语句或是函数,问题如下:
---------------------------------
BOM结果列转行,求一语句或函数A01,A02,A03 三个字段,分别为主件,序号,元件
记录
f01,f02, f03
a01 0001 a1
a01 0002 a2
a02 0001 a5
a02 0002 a6
a02 0003 a7
a02 0004 a8
需要的结果显示两个字段,具体如下:
f01 f03
a01 a1,a2
a02 a5,a6,a7,a8
---------------------------------
BOM结果列转行,求一语句或函数A01,A02,A03 三个字段,分别为主件,序号,元件
记录
f01,f02, f03
a01 0001 a1
a01 0002 a2
a02 0001 a5
a02 0002 a6
a02 0003 a7
a02 0004 a8
需要的结果显示两个字段,具体如下:
f01 f03
a01 a1,a2
a02 a5,a6,a7,a8
create table ann
(f01 varchar(6),
f02 varchar(6),
f03 varchar(6))
insert into ann
select 'a01', '0001', 'a1' union all
select 'a01', '0002', 'a2' union all
select 'a02', '0001', 'a5' union all
select 'a02', '0002', 'a6' union all
select 'a02', '0003', 'a7' union all
select 'a02', '0004', 'a8'
create function dbo.fn_ann
(@f01 varchar(6))
returns varchar(200)
as
begin
declare @r varchar(200)
select @r=isnull(@r,'')+f03+','
from ann
where f01=@f01
select @r=left(@r,len(@r)-1)
return @r
end
select f01,
dbo.fn_ann(f01) 'f03'
from ann
group by f01/*
f01 f03
------ ----------------
a01 a1,a2
a02 a5,a6,a7,a8(2 row(s) affected)
*/