CODEWITHSUNDEEP
sqlsql-serverstring-aggregationlistaggstring-agg
drk
drk

Reputation: 117

STRING_AGG with OVER() clause in SQL Server

Is there any equivalent of Postgres: STRING_AGG with OVER() or Oracle LISTAGG with OVER() in SQL Server? I would like to concatenate text values without grouping it, so I need STRING_AGG in analytical version, but according to docs is not supported in SQL Server:

The OVER clause may follow all aggregate functions, except the STRING_AGG, GROUPING or GROUPING_ID functions.

Do I need to create subquery in my SELECT or is there better approach for that?

Upvotes: -1

Views: 1576

Answers (1)

Thom A
Thom A

Reputation: 95989

No, STRING_AGG is only implemented as an aggregate function (at time of writing) with support for the WITHIN GROUP clause, it cannot be windowed (using an OVER clause). You will need to use a subquery/CTE/derived table:

SELECT YT.YourID,
       YT.YourGroupingColumn,
       DT.AggregatedString
FROM dbo.YourTable YT
     OUTER APPLY (SELECT STRING_AGG(YourAggregatingColumn,',') AS AggregatedString
                  FROM dbo.YourTable sq
                  WHERE sq.YourGroupingColumn = YT.YourGroupingColumn) DT;

SELECT YT.YourID,
       YT.YourGroupingColumn,
       DT.AggregatedString
FROM dbo.YourTable YT
     LEFT JOIN (SELECT sq.YourGroupingColumn,
                       STRING_AGG(sq.YourAggregatingColumn,',') AS AggregatedString
                FROM dbo.YourTable sq
                GROUP BY sq.YourGroupingColumn) DT ON YT.YourGroupingColumn = DT.YourGroupingColumn;
WITH Agg AS(
     SELECT sq.YourGroupingColumn,
            STRING_AGG(sq.YourAggregatingColumn,',') AS AggregatedString
     FROM dbo.YourTable sq
     GROUP BY sq.YourGroupingColumn)
SELECT YT.YourID,
       YT.YourGroupingColumn,
       A.AggregatedString
FROM dbo.YourTable YT
     LEFT JOIN Agg A ON YT.YourGroupingColumn = A.YourGroupingColumn;

With no sample data, I can't test what would be most performant for you; you will need to do that off your own back (I expect the CTE and Derived table to have very similar, if not identical, query plans though).

Upvotes: 0

Related Questions