create table t_source_bj (库存编号 varchar(10),名称 varchar(20),零件备注码1 varchar(20),零件备注码2 varchar(20),零件备注码3 varchar(20),零件备注码4 varchar(20),合并项 varchar(120));

insert into t_source_bj values('0704008','产品1','a','b','b','c','abbc');
insert into t_source_bj values('07043255','产品2','a','b','b','c','abbc');
insert into t_source_bj values('0702348','产品22','a','f','b','c','afbc');
insert into t_source_bj values('07432408','产品3','a','b','d','c','abdc');
insert into t_source_bj values('0723438','产品0','a','c','b','c','acbc');

create table t_source_bj (
库存编号 varchar(10),
名称 varchar(20),
零件备注码1 varchar(20),
零件备注码2 varchar(20),
零件备注码3 varchar(20),
零件备注码4 varchar(20),
合并项 varchar(120)
);

insert into t_source_bj values('0704008','产品1','a','b','b','c',null);
insert into t_source_bj values('07043255','产品2','a','b','b','c',null);
insert into t_source_bj values('0702348','产品22','a','f','b','c',null);
insert into t_source_bj values('07432408','产品3','a','b','d','c',null);
insert into t_source_bj values('0723438','产品0','a','c','b','c',null);

;with t
as(
select 库存编号,名称,零件备注码1 as 零件备注码 from t_source_bj
union 
select 库存编号,名称,零件备注码2 as 零件备注码 from t_source_bj
union 
select 库存编号,名称,零件备注码3 as 零件备注码 from t_source_bj
union 
select 库存编号,名称,零件备注码4 as 零件备注码 from t_source_bj
),
m as(
SELECT *FROM (SELECT DISTINCT 库存编号,名称 FROM t)A
OUTER APPLY(
    SELECT 零件备注码= STUFF(REPLACE(REPLACE(
            (   SELECT 零件备注码 FROM t N
                WHERE 库存编号 = A.库存编号 and 名称=A.名称
                FOR XML AUTO
             ), '<N 零件备注码="', ','), '"/>', ''), 1, 1, '')
)N
)
update t_source_bj 
set 合并项=零件备注码 from m 
where t_source_bj.库存编号=m.库存编号 and t_source_bj.名称=m.名称

select * from t_source_bj

/*
库存编号    名称    零件备注码1    零件备注码2    零件备注码3    零件备注码4    合并项
0704008    产品1    a    b    b    c    a,b,c
07043255    产品2    a    b    b    c    a,b,c
0702348    产品22    a    f    b    c    a,b,c,f
07432408    产品3    a    b    d    c    a,b,c,d
0723438    产品0    a    c    b    c    a,b,c
*/

------解决方案--------------------

SQL code
select 
[你要的编码] = isnull((
        select s+'' from (
        select s=零件备注码1 from t_source_bj b where a.库存编号=b.库存编号
        union 
        select s=零件备注码2 from t_source_bj b where a.库存编号=b.库存编号
        union
        select s=零件备注码3 from t_source_bj b where a.库存编号=b.库存编号
        union
        select s=零件备注码4 from t_source_bj b where a.库存编号=b.库存编号
        ) t
    for xml path(''))
    ,''),
* from t_source_bj a