新人请教1条SQL语句

[liushiying18]

有3个表
表1，Tb_Product
ProductID	ProductName
00000001	aa
00000002	bb

表2，Tb_ProductStructure
ProductID	MaterielTypeID2
00000001	0001-0015
00000001	0001-0001
00000002	0001-0012
00000002	0001-0010
00000002	0002-0002

表3，Tb_MaterielType2
MaterielTypeID2	MaterielTypeID	MaterielTypeName2
0001-0001	0001	PE
0001-0010	0001	CPP
0001-0012	0001	PET
0001-0015	0001	ON
0002-0002	0002	PE


想返回下面结果，怎么样实现啊，谢谢
ProductID	ProductName	MaterielTypeName2
00000001	aa	ON/PE
00000002	bb	PET/CPP/PE

[liushiying18]

为什么要加上group by a.ProductID,a.ProductName

[zbdzjx]

看一下这个：http://bbs.csdn.net/topics/392062276 将3个表连接，用上面页面中的合并列方式，将MaterielTypeName2列合并。

[xxfvba]

;with Tb_Product (ProductID , ProductName) as (select '00000001', 'aa' union all select '00000002', 'bb'), Tb_ProductStructure(ProductID, MaterielTypeID2) as (select '00000001', '0001-0015' union all select '00000001', '0001-0001' union all select '00000002', '0001-0012' union all select '00000002', '0001-0010' union all select '00000002', '0002-0002'), Tb_MaterielType2(MaterielTypeID2, MaterielTypeID, MaterielTypeName2) as (select '0001-0001', '0001', 'PE' union all select '0001-0010', '0001', 'CPP' union all select '0001-0012', '0001', 'PET' union all select '0001-0015', '0001', 'ON' union all select '0002-0002', '0002' , 'PE') select a.*, STUFF((select '/'+MaterielTypeName2 from Tb_MaterielType2 c,Tb_ProductStructure b where c.MaterielTypeID2=b.MaterielTypeID2 and a.ProductID=b.ProductID for XML path('')),1,1,'') from Tb_Product a group by a.ProductID,a.ProductName