STRING_AGG replacement in SQL Server 2016

Question

I would like to aggregate single column values with an separator in between and with some prefix. Below is the code tried which works.

SELECT concat('TNB/IAG/',STRING_AGG(WF_ValStr, '/')) AS Result
    FROM wfattrdata where wf_id=35262472 and  WF_AttrID in (28,29,30,31,33);

I could get the below expected result in latest MSSql versions.

TNB/IAG/1/2/3/4/5

How to replace the above query to make it work in SQL Server 2016?

Solution

Check the below STUFF,XML code -

SELECT DISTINCT CONCAT('TNB/IAG/',T1.results) AS EXPECTED_RESULT FROM
(
select REPLACE(STUFF(CAST((
                    SELECT   ' /' +CAST(c.WF_ValStr AS VARCHAR(MAX))
                    FROM (  
                            SELECT distinct WF_ValStr
                            FROM wfattrdata
                        ) c
                    FOR XML PATH(''), TYPE) AS VARCHAR(MAX)), 1, 2, ''),' ','') AS results
                    from wfattrdata t) T1;

Comments

[Panagiotis Kanavos]

There are a lot of duplicate questions that show how to use tricks with XML or custom SQLCLR functions. All of them, one way or another, describe one of the techniques in Aaron Bertrand's Grouped Concatenation in SQL Server.

[Panagiotis Kanavos]

On the other hand, storing attribute values in separate rows is actually a design problem. Instead of flexibility, you lose the ability to easily query the data and end up with bad performance. SQL Server supports thousands of columns through sparse columns. You can easily store semi-structured data with XML or JSON, index it and query it quickly.

[eshaa]

@PanagiotisKanavos I don't want to use function and trying to aggregate just one column. The page you shared is to concat two columns based on id and values.

[Panagiotis Kanavos]

On the contrary, it describes how to aggregate columns. Exactly what STRING_AGG does. In fact, trying to aggregate multiple columns is a real pain without STRING_AGG

[Panagiotis Kanavos]

Check the answer just posted for example. It's the XML PATH technique described in Aaron Bertrand's article. I can't stress this enough - all the answers you'll find come from this article

[Panagiotis Kanavos]

Speaking of JSON, you could use FOR JSON and get an easily-parsed JSON string. You could replace the brackets and quotes to get the format you want, especially if you use WITHOUT_ARRAY_WRAPPER to get rid of the array wrapper.