sql-server - STRING_AGG 与 CASE WHEN

Question

架构

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);

在这个简化的方案中，关系设置为 1 的人是主要保单持有人，而不同的数字显示其他人与主要保单持有人(配偶、子女等)的关系。

问题

显示每个主要保单持有人的所有家属，并将他们分组到任意选择的年龄组中。

所需的输出:

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

我到目前为止的努力:

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

我不知道如何使用 CASE WHEN与 STRING_AGG .

当我尝试类似的东西时

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

服务器正确地抗议

contained in either an aggregate function or the GROUP BY clause

但是当然按它分组也不能解决问题，所以我缺少一个技巧。此外，我确信可以以更简单的方式编写主查询本身。

编辑 - 解决方案

正如@Gserg 正确指出的那样，以及我在发布问题后不久意识到的，解决方案非常简单，要求在 STRING_AGG 中使用 CASE WHEN 而不是相反。多。

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

仍在寻找如何改进我的原始查询的建议和想法。

Answer 1

使用 iif 最大化单个条件的函数。

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