Has anyone figured out how to remove duplicates when using String_AGG or is there a different function I can use besides XML Path? I am trying to stay away from XML Path as we had issues with BAQ using this. I figured out how to sort the string using “within Group(Order By…” but cannot figure out how to remove duplicates.
Likely requires a CTE query.
Do you (or anyone) have an example of a BAQ using CTE subquery and STRING_AGG? I got the CTE subquery to work and returns distinct values but I am struggling with the STRING_AGG…should it go in the CTE subquery or Top Level?
I was able to figure out how to setup the CTE and InnerSubQuery in order to remove the duplicates. I used the CTE in the InnerSubQuery along with “SubQuery Criteria” linking to the CTE fields to the Top Level query.
The InnerSubQuery contained a calculated field using the STRING_AGG to string the distinct values together into one column. Then in the TopLevel I had another calculated field to display the InnerSubQuery calculated field. Quite involved but it does work.