SQL Server - STRING_AGG separator as conditional expression

Question

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

I'm pretty sure this cannot be done, but if it could be done, you would most like hit [the bug](https://stackoverflow.com/a/52534215/11683) anyway. — GSerg, Feb 10 '19 at 13:26
@GSerg Thanks for bringing this up. I am aware of that bug and it is already on Azure Feedback. — Lukasz Szozda, Feb 10 '19 at 19:16

Zohar Peled · Accepted Answer · 2019-02-10T19:40:18.630

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

I like this aproach. Single-level query is always more reliable and easier to read. The only minor drawback is that I have to use same length of delimeter (' OR '/' AND ') when I got cases were I am unable to determine which goes first. Here is simplified scenario i%2 so we could use hardcoded 5. — Lukasz Szozda, Feb 10 '19 at 19:19
That is a drawback. I had a feeling my idea was so simple you should have thought about it yourself...I've updated my answer. — Zohar Peled, Feb 10 '19 at 19:40
The other solution is to pad separator with trailing spaces up to longest possible separator. Ugly but it will work — Lukasz Szozda, Feb 10 '19 at 19:43

Lukasz Szozda · Answer 2 · 2019-02-10T19:21:23.773

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

SQL Server - STRING_AGG separator as conditional expression

2 Answers2