Simple example
SELECT STRING_AGG(RTRIM(itemnmbr),', ')
FROM SOP10200 l
WHERE sopnumbe = 'std10119419'
Get holds for a group of orders:
SELECT h.sopnumbe, ho.HoldType
FROM SOP10100 h
LEFT JOIN (
SELECT
ho.sopnumbe, ho.soptype,
STRING_AGG(RTRIM(ho.PRCHLDID), ',') as HoldType
FROM sop10104 ho
WHERE ho.DELETE1 = 0
GROUP BY ho.SOPNUMBE, ho.soptype
) ho ON ho.SOPNUMBE = h.sopnumbe AND ho.soptype = h.SOPTYPE
WHERE h.SOPNUMBE LIKE 'STD101300%'
