Hello,
I've found useful function LISTAGG below to get all email addresses (t$info) in one row ";" separated, ready to use and I can see expected results in oracle SQL developer BUT when I call this as a view by MS SQL Link server I do not see any data for that column. I know it could be an issue by MS SQL conversion but I think it's related to the big column type and size list agg generate automatically (varchar2 4000 char). So I'm asking how to convert result in a more compatible way such like NVARCHAR2(). Thanks.
SELECT cnbp.t$bpid bpid,
LISTAGG(t$info, ';') WITHIN GROUP (ORDER BY cnbp.t$bpid) info
FROM baan.ttccom145xxx cnbp, baan.ttccom140xxx cnt
where cnbp.t$ccnt = cnt.t$ccnt
and cnbp.t$cmsk$4 = 1 -- (only pay-by)
GROUP BY cnbp.t$bpid
order by 1 desc