String_Agg For SQL Server Before 2017

String_agg for SQL Server before 2017

In SQL Server pre-2017, you can do:

select stuff( (select ',' + cast(t.id as varchar(max))
               from tabel t
               for xml path ('')
              ), 1, 1, ''
            );

The only purpose of stuff() is to remove the initial comma. The work is being done by for xml path.

Substitute for STRING_AGG pre SQL Server 2016

Here's a solution you can try with for xml path

Basically select and group the json columns needed and using an apply, use the for xml path solution to aggregate the correlated ID values; because the outer query needs to refer to the output of the apply it needs to be aggregated also, I chose to use max

select max(x.Ids), (select country,city for json path) as [Json]
from t
outer apply (
    select Stuff((select ',' + Convert(varchar(10),t2.Id)
    from t t2
    where t2.city=t.city and t2.country=t.country
    for xml path(''),type).value('(./text())[1]','varchar(10)'),1,1,'') as Ids
)x
group by country,city

Working Fiddle

SQL Server 2017 STRING_AGG Order By

I can repro this on SQL Server 2019 build 15.0.4198.2 too.

It just requires the query to be run in the context of a database set to COMPATIBILITY_LEVEL of 100 (2008).

All other (later) compatibility levels work.

The documentation states

STRING_AGG is available in any compatibility level.

And doesn't mention this.

If you are unable to change the compatibility level of the database to something higher then possibly you will need to fall back to the old XML PATH method of concatenating ordered results.

Port STRING_AGG to FOR XML

Than you for your help.

The right question was:

SET @DynamicQuery = CONCAT (N'
SELECT @Sample =  STUFF((SELECT '', ''+ t.ColumnData  
FROM (
        SELECT TOP 5 CAST(x.[', @CurrentColumnName, '] AS VARCHAR(MAX)) AS ColumnData 
        FROM ', @CurrentTableName, ' AS x 
        WHERE x.[', @CurrentColumnName, '] IS NOT NULL 
) AS t
   FOR XML PATH('''')),1,1,'''')')

Create a User defined function like SQL server 2017 STRING_AGG on earlier versions

Ok.. so with the first comment of @MichałTurczyn I run into this Microsoft article about CLR User-Defined Aggregate - Invoking Functions

Once I compile the code into SrAggFunc.dll, I was trying to register the aggregate in SQL Server as follows:

CREATE ASSEMBLY [STR_AGG] FROM 'C:\tmp\STR_AGG.dll'; 
GO

But I got the following error.

Msg 6501, Level 16, State 7, Line 1

CREATE ASSEMBLY failed because it could not open the physical file 'C:\tmp\SrAggFunc.dll': 3(The system cannot find the path specified.).

So I used this excellant part of @SanderRijken code and then change the command to

CREATE ASSEMBLY [STR_AGG] 
FROM 0x4D5A90000300000004000000FF......000; --from GetHexString function
GO

and then,

CREATE AGGREGATE [STR_AGG] (@input nvarchar(200)) RETURNS nvarchar(max) 
EXTERNAL NAME [STR_AGG].C_STRING_AGG;`

Now it's done.

You can see it under your Database -> Programmability on SSMS

and used like :

SELECT a.Id, [dbo].[STR_AGG](c.Desc) cDesc
FROM TableA a
JOIN TableB b on b.aId = a.Id
JOIN TableC c on c.Code = b.bCode 
GROUP BY a.Id

Thanks all =)

SQL Server STRING_AGG() : equivalent in Linq

In question not given any information about which ORM you are using, so i try to show one example to this case in linq to objects in c#, not fully solution for question

String_Agg aggregation function takes a set of values and combines them into a string, using a supplied separator. In LINQ (linq to objects) you may do it by using Group By,
i think this example helps you:

class Program
    {
        static void Main(string[] args)
        {
            var emails = new List<EmailData>() { 
                new EmailData() {Name="Sam", Email="Email1" },
                new EmailData() {Name="Sam", Email="Email2" },
                new EmailData() {Name="Ted", Email="Email3" },
                new EmailData() {Name="Sam", Email="Email4" },
                new EmailData() {Name="Sam", Email="Email5" }
            };
            var grouping = emails.GroupBy(x => x.Name);
            foreach (var group in grouping)
            {
                Console.WriteLine(
                    $"{group.Key}: {string.Join(", ", group.Select(x => x.Email))}");
            }
            Console.ReadKey();
        }
    }
    public class EmailData
    {
        public string Name { get; set; }
        public string Email { get; set; }
    }

Result:

STRING_AGG replacement in SQL Server 2016

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;

---

Related Topics