A request that we have seen many times when creating SOP invoices for Dynamics GP is to concatenate serial numbers. So if there is a item with serial numbers, instead of showing a list with one serial number per line under the item, we want to show all the serial numbers on one line, separated by a comma. Below is the SQL code that will do this in SQL 2005 and 2008:
SELECT p.SOPNUMBE SOP_Number, p.SOPTYPE SOP_Type,
p.ITEMNMBR Item, p.LNITMSEQ Line_Item_Sequence,
LEFT(p.serial_numbers, len(p.serial_numbers)-1) Serial_Numbers
FROM (SELECT p1.SOPNUMBE, p1.SOPTYPE, p1.ITEMNMBR, p1.LNITMSEQ,
(SELECT coalesce(rtrim(SERLTNUM) + ', ','') FROM SOP10201 p2
WHERE p1.SOPNUMBE = p2.SOPNUMBE
and p1.SOPTYPE = p2.SOPTYPE
and p1.ITEMNMBR = p2.ITEMNMBR
and p1.LNITMSEQ = p2.LNITMSEQ
ORDER BY SOPNUMBE FOR XML PATH('')) AS serial_numbers
FROM SOP10201 p1
GROUP BY SOPNUMBE, SOPTYPE, ITEMNMBR, LNITMSEQ) p You can add this code to your SQL view or stored procedure and the result will be a string with concatenated serial numbers. |