in V9, we had some SQL Scalar-valued Functions like the below one. For example it takes CoNum and returns all the Salesmen in comma separated string.
CREATE Function [dbo].[xxx_GetSlsmanFn] (@CO CoNumType)RETURNS nvarchar(max)AsBEGIN declare @tmp varchar(100) SET @tmp = '' Select @tmp = @tmp + A.slsman+ ' (' + coalesce(C.Name,'') + '), ' from CO A left join slsman_mst B ON A.slsman=B.slsman left join VendAddr C on B.ref_num = C.vend_num where A.co_num = @CO Return SUBSTRING(@tmp, 0, LEN(@tmp))
END
Previously in V9, dbo.xxx_GetSlsmanFn(CoNum) in a derived IDO property used to work, but not now.
How can I convert it into a Derived IDO Property? i.e. from a sub-collection, comma separated values of a property?
In the newer versions of SQL, there is the STRING_AGG function which can be used to create a comma separated list.
Below is a derived property which creates a CSL of serial numbers. (P.S. this does work…
Thanks Noah, This is awesome. Thanks for teaching me about this. Problem solved.