先放上图,在开发过程中遇到了这样的问题,想要由第一个表实现第二张表的样子,然后研究了好久的stuff函数,终于研究出来了,如果是mysql的话,有一个group_concat方法更为简单。
接下来就是代码:
selectitem_code,STUFF(( SELECT ',' + acquisitionNo FROM [Table_1] tWHERE t.item_code =[Table_1].item_code group by acquisitionNo FOR xml path('')),1, 1, '') AS acquisitionNo,--描述STUFF(( SELECT ',' + descripteing FROM [Table_1] tWHERE t.item_code =[Table_1].item_code group by descripteing FOR xml path('')),1, 1, '') AS descripteing,--描述STUFF(( SELECT ',' + material_supplierInfo1 FROM [Table_1] tWHERE t.item_code =[Table_1].item_code group by material_supplierInfo1 FOR xml path('') ),1, 1, '') AS material_supplierInfo --材料供应生产商from [Table_1]group by item_code
注意:一定要给表重命名,并且字段要加上前缀表名,不然会出现分组失败,因为这点也踩坑找了好久。