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

2 answers

  • answered 2019-02-10 12:39 Lukasz Szozda

    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

  • answered 2019-02-10 14:09 Zohar Peled

    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