3

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?

eshaa
  • 386
  • 2
  • 7
  • 26
  • 2
    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](https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation). – Panagiotis Kanavos Oct 07 '19 at 10:16
  • 1
    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. – Panagiotis Kanavos Oct 07 '19 at 10:20
  • @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. – eshaa Oct 07 '19 at 10:24
  • 1
    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 Oct 07 '19 at 10:29
  • 1
    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 Oct 07 '19 at 10:29
  • 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. – Panagiotis Kanavos Oct 07 '19 at 10:31

1 Answers1

4

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;
Arulmouzhi
  • 1,878
  • 17
  • 20