SQL - Dynamics NAV - Row Combine - How to combine a single field with multiple rows into a long string with | to use in filters

https://tableplus.com/blog/2018/10/concatenate-multiple-rows-from-subquery-to-a-string-sql-server.html

Example2

SELECT p1.grp,

          ( SELECT [Item No_] + '|'  + [BI_ItemQty] where QtyOnHand <> 0 ) p2

             WHERE p2.grp = p1.grp

             ORDER BY [Item No_]

               FOR XML PATH('') ) AS AllComm

      FROM (select [Item No_], 1 as grp from [BI_ItemQty] where QtyOnHand <> 0 ) p1

     GROUP BY grp ;

To combine all rows in a table

--------------------------------------

SELECT distinct

       (STUFF((SELECT distinct CAST(', ' + [Location Code] AS VARCHAR(MAX)) 

         FROM [BI-ItemQty]

         --WHERE (item_id = products.item_id) 

         FOR XML PATH ('')), 1, 2, '')) AS AllComm

FROM [BI-ItemQty]