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

Answer 1

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