T-SQL XML Path Solution to BOM Notes to a Single Row
SELECT SS.PARPRT_02, SS.COMPRT_02, SS.EFFDTE_02 , SS.QTYPER_02, (SELECT ‘~’ + US.NOTES_61 FROM Windows_Notes US WHERE US.PRTNUM_61 = SS.PARPRT_02 AND US.COMPRT_61 = SS.COMPRT_02 AND US.EFFDTE_61 = SS.EFFDTE_02 AND US.TYPE_61 = ‘BM’ FOR XML PATH(”) ) [BOM/NOTES] FROM Product_Structure SS WHERE PARPRT_02 <> ” GROUP BY SS.PARPRT_02, SS.COMPRT_02, SS.EFFDTE_02, SS.QTYPER_02 ORDER BY 1, 2 Vincent Stefanetti Senior Consultant…