STRING_AGG with CASE WHEN

Question

The schema

CREATE TABLE person
(
     [first_name] VARCHAR(10), 
     [surname] VARCHAR(10), 
     [dob] DATE, 
     [person_id] INT
);

INSERT INTO person ([first_name], [surname], [dob] ,[person_id]) 
VALUES
    ('Alice', 'AA', '1/1/1960', 1),
    ('Bob'  , 'AA', '1/1/1980', 2),
    ('Carol', 'AA', '1/1/2018', 3),
    ('Dave' , 'BB', '1/1/1960', 4),
    ('Elsa', ' BB', '1/1/1980', 5),
    ('Fred' , 'BB', '1/1/1990', 6),
    ('Gina' , 'BB', '1/1/2018', 7);

CREATE TABLE person_membership
(
    [person_id] INT, 
    [personstatus] VARCHAR(1), 
    [membership_id] INT, 
    [relationship] INT
);

INSERT INTO person_membership ([person_id], [personstatus], [membership_id], [relationship])
VALUES
    (1, 'A', 10, 1),
    (2, 'A', 10, 2),
    (3, 'A', 10, 3),
    (4, 'A', 20, 1),
    (5, 'A', 20, 2),
    (6, 'A', 20, 4),
    (7, 'A', 20, 5);

In this simplified scheme the person with relationship set to 1 one is the main policy holder while different numbers show how other people are related to the main policy holder (spouse, children etc.)

The problem

Show all dependants for each main policy holder and group them within arbitrarily chosen age groups.

The desired output:

person_id|membership_id|first_name|dependants under 10|dependants over 10
---------+-------------+----------+-------------------+-------------------
       1 |          10 |   Alice  |               Bob |            Carol
       4 |          20 |    Dave  |              Gina |       Elsa, Fred
       8 |          30 |   Helen  |     Ida, Joe, Ken |             NULL

My efforts so far:

SELECT 
    sub.person_id, sub.membership_id, sub.first_name, 
    STRING_AGG (sub.dependant, ',')
FROM
    (SELECT 
         person.person_id, person_membership.membership_id, 
         person.first_name, p.first_name AS 'dependant', 
         DATEDIFF(yy, CONVERT(DATETIME, p.dob), GETDATE()) AS 'age'
     FROM   
         person
     LEFT JOIN 
         person_membership ON person.person_id = person_membership.person_id
     LEFT JOIN 
         memship  ON person_membership.membership_id = memship.membership_id
     LEFT JOIN 
         person_membership pm ON person_membership.membership_id = pm.membership_id AND pm.relationship > 1
     LEFT JOIN 
         person p ON pm.person_id = p.person_id
     WHERE 
         person_membership.relationship = 1) as sub
GROUP BY 
    sub.person_id, sub.membership_id, sub.first_name

I can't figure out how to use CASE WHEN with STRING_AGG.

When I try something like

"CASE WHEN age < 10 THEN STRING_AGG (sub.dependant, ',') ELSE NULL END as 'Under 10'"

the server rightly protests that

contained in either an aggregate function or the GROUP BY clause

but of course grouping by it doesn't solve the problem either so there is a trick that I am missing. Also I'm sure it's possible to write the main query itself in a simpler way.

Edit - solution

As @Gserg rightly pointed out, and what I have realised moments after posting the question, the solution is very simple and calls for using CASE WHEN within STRING_AGG and not the other way around. Doh.

string_agg(case when age < 10 then sub.dependant else null end, ', ') as 'Under 10'

Still still looking for suggestions and ideas how to improve on my original query.

Answer 1

maximize using the iif function for a single condition.

SELECT sub.person_id, sub.membership_id, sub.first_name, 
    STRING_AGG (iif(age < 10, sub.dependant, null), ',') 'Under 10'
FROM   (SELECT person.person_id, person_membership.membership_id, person.first_name, p.first_name AS 'dependant', 
        DATEDIFF(yy,CONVERT(DATETIME, p.dob),GETDATE()) AS 'age'

        FROM   person

        LEFT JOIN person_membership ON person.person_id = person_membership.person_id
        LEFT JOIN person_membership  memship  ON person_membership.membership_id = memship.membership_id
        LEFT JOIN person_membership pm ON person_membership.membership_id = pm.membership_id AND pm.relationship > 1
        LEFT JOIN person p             ON pm.person_id = p.person_id
        WHERE person_membership.relationship = 1) as sub
GROUP BY sub.person_id, sub.membership_id, sub.first_name