CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2017string-aggregation
Lukasz Szozda
Lukasz Szozda

Reputation: 176214

SQL Server - STRING_AGG separator as conditional expression

I need to concatenate rows using different separator based on arbitrary logic. For instance:

CREATE TABLE t(i INT, c VARCHAR(10));
INSERT INTO t(i,c) VALUES(1, 'a'),(2, 'b'),(3, 'c'),(4, 'd'),(5, 'e');

SELECT STRING_AGG(c,(CASE WHEN i%2=0 THEN ' OR ' ELSE ' AND ' END)) 
       WITHIN GROUP (ORDER BY i) AS r
FROM t;

db<>fiddle demo

And it ends with error:

Separator parameter for STRING_AGG must be a string literal or variable.

My end goal is to get: a OR b AND c OR d AND e like in: db<>fiddle demo

Notes: I am aware of XML + STUFF or @var = @var + ....

I am searching for "workarounds" specific to STRING_AGG.

EDIT: I've added it as sugestion on Azure Feedback

Upvotes: 0

Views: 5614

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82534

You are almost there. Just reverse the order and use stuff and you can eliminate the need for a cte and most of the string functions:

SELECT STUFF(
           STRING_AGG(
               (IIF(i % 2 = 0, ' OR ', ' AND '))+c
           , '') WITHIN GROUP (ORDER BY i)
           , 1, 5, '') AS r
FROM t;

Results: a OR b AND c OR d AND e

db<>fiddle demo

Since the first row i % 2 equals 1, you know the string_agg result will always start with and: and a or b... Then all you do is remove the first 5 chars from that using stuff and you're home free.

I've also taken the liberty to replace the CASE expression with the shorter IIF

Update

Well, in the case the selected separator is not known in advance, I couldn't come up with a single query solution, but I still think I found a simpler solution than you've posted - separating my initial solution to a cte with the string_agg and a select from it with the stuff, while determining the length of the delimiter by repeating the condition:

WITH CTE AS
(
SELECT MIN(i) As firstI,
       STRING_AGG(
               (IIF(i % 2 = 0, ' OR ', ' AND '))+c
           , '') WITHIN GROUP (ORDER BY i)
       AS r
FROM t
)

SELECT STUFF(r, 1, IIF(firstI % 2 = 0, 4, 5), '') AS r
FROM CTE;

db<>fiddle demo #2

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176214

One possible solution(not ideal) is to move separator to the main expression and set separator as blank:

-- removing last separator
WITH cte AS (
SELECT STRING_AGG(c+(CASE WHEN i%2=0 THEN ' OR ' ELSE ' AND ' END), '')
    WITHIN GROUP (ORDER BY i)AS r
FROM t
)
SELECT r, REPLACE(REPLACE(r+'$', ' OR $', ''), ' AND $', '') AS r
    ,STUFF(r,
           LEN(r)-CHARINDEX(' ', REVERSE(TRIM(r)))+1, 
           CHARINDEX(' ', REVERSE(TRIM(r)))+1,
           '') AS r
FROM cte;

db<>fiddle demo

Upvotes: 0

Related Questions