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

Manufacturing Division
ECi Software Solutions, Inc.
Vince’s MAX Blog:

Office 650-286-2605, Mobile 408-335-5737

www.ECiSolutions.com

About Vincent Stefanetti

Vincent Stefanetti Senior Consultant MAX Manufacturing Software Working with MAX and Related Technologies Since 1987 MAX ERP System Implementations and Projects MAX, Dynamics GP, Synergy, Event Manager, Crystal Reports, SQL Reporting Services, Database and System Migrations and Updates, MAX Training Classes - All Levels, Custom Reporting.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply