if object_id('[part]') is not null drop table [part]
go
create table [part]([ID] int,[partname] varchar(1))
insert [part]
select 1,'A' union all
select 2,'B' union all
select 3,'C'
go
if object_id('[produce]') is not null drop table [produce]
go
create table [produce]([ID] int,[producename] varchar(1))
insert [produce]
select 1,'E' union all
select 2,'F'
go
if object_id('[relation]') is not null drop table [relation]
go
create table [relation]([产品id] int,[零件1id] int,[数量1] int,[零件2id] int,[数量2] int)
insert [relation]
select 1,1,1,2,2 union all
select 2,1,1,3,3
go

select b.producename as 产品,
  a.partname+'*'+ltrim(c.[数量1])+'+'+d.partname+'*'+ltrim(c.[数量2]) as 零件
from relation c
left join produce b on c.产品id=b.id
left join part a on a.id=c.[零件1id]
left join part d on d.id=c.[零件2id]

/**
产品   零件
---- -----------------------------
E    A*1+B*2
F    A*1+C*3

(2 行受影响)
**/