求一条查询话语····

求一条查询语句····
零件（part）表的字段是
  ID partname 
  1 A
  2 B
  3 C
产品表（produce）的字段是
  ID producename
  1 E
  2 F

关系表（relation） 的字段是 
  产品id 零件1id 数量1 零件2id 数量2
  1 1 1 2 2
  2 1 1 3 3


怎么用查询语句输出： 
 产品 零件
  E A*1+B*2
  F A*1+C*3  


[解决办法]

SQL code

if object_id('[part]') is not null drop table [part]gocreate table [part]([ID] int,[partname] varchar(1))insert [part]select 1,'A' union allselect 2,'B' union allselect 3,'C'goif object_id('[produce]') is not null drop table [produce]gocreate table [produce]([ID] int,[producename] varchar(1))insert [produce]select 1,'E' union allselect 2,'F'goif object_id('[relation]') is not null drop table [relation]gocreate table [relation]([产品id] int,[零件1id] int,[数量1] int,[零件2id] int,[数量2] int)insert [relation]select 1,1,1,2,2 union allselect 2,1,1,3,3goselect b.producename as 产品,  a.partname+'*'+ltrim(c.[数量1])+'+'+d.partname+'*'+ltrim(c.[数量2]) as 零件from relation cleft join produce b on c.产品id=b.idleft join part a on a.id=c.[零件1id]left join part d on d.id=c.[零件2id]/**产品   零件---- -----------------------------E    A*1+B*2F    A*1+C*3(2 行受影响)**/