using GROUP_CONCAT with separator gives an error in SFMC-CodePudding

This is my table -

item      item_id   components
chair     1001      wood
chair     1001      screw
chair     1001      paint
book      1002      pages
book      1002      bind

Based on the item and item_id, I want to concat components together using comma (,). For example i should get following output

item    item_id    components

chair   1001       wood,screw,paint
book    1002       pages,bind

Below is the code that I have right now. I am running this on SFMC and i get an error - Error saving the Query field.Incorrect syntax near 'SEPARATOR'

SELECT item_id, GROUP_CONCAT(components SEPARATOR ',')
FROM table
GROUP BY item_id

Pleaser can anyone help me a way to run this query without errors and get the desired results?

CodePudding user response：

There is no GROUP_CONCAT in SQL Server.

The equivalent of MySQL GROUP_CONCAT is STRING_AGG in SQL Server

Try:

SELECT item_id, 
       string_agg(components,  ',') as components
FROM my_table
GROUP BY item_id

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b4a37873ec09b38e77c61c0053d7ed24

Solution for versions older than SQL Server 2017:

SELECT item_id
      , components = STUFF((
          SELECT ','   mt.components
          FROM my_table mt
          WHERE m.item_id = mt.item_id
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM my_table m
group by item_id;

https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=cd30b7f2335171273f3a8bbe2930d1cf