The following query

DECLARE @SNH TABLE 
(
    dt date, 
    QueueName varchar(10),
    SN varchar(10)
)

INSERT INTO @SNH (Dt, QueueName, SN)
VALUES ('2001-04-04', 'Queue01', 'Q01SN01'),
       ('2001-04-05', 'Queue01', 'Q01SN01'),
       ('2001-04-06', 'Queue01', 'Q01SN01'),
       ('2001-04-04', 'Queue02', 'Q02SN01'),
       ('2001-04-05', 'Queue02', 'Q02SN01'),
       ('2001-04-06', 'Queue02', 'Q02SN02')

DECLARE @QH TABLE 
(
    DT date, 
    QueueName varchar(10)
)  

INSERT INTO @QH(DT, QueueName)
VALUES ('2001-04-04','Queue01'),
       ('2001-04-05','Queue01'),
       ('2001-04-06','Queue01'),
       ('2001-04-04','Queue02'),
       ('2001-04-05','Queue02'),
       ('2001-04-06','Queue02')

SELECT DISTINCT 
    q.QueueName clnQueueName,
    MIN(q.Dt) OVER (PARTITION BY q.QueueName) clnStartDate,
    MAX(q.Dt) OVER (PARTITION BY q.QueueName) clnEndDate,
    s.SN
FROM 
    @QH q
LEFT JOIN 
    @SNH s ON s.QueueName = q.QueueName 

returns this output:

which I'm aiming to aggregate into a comma separated list with

SELECT DISTINCT 
    q.QueueName clnQueueName,
    MIN(q.Dt) OVER (PARTITION BY q.QueueName) clnStartDate,
    MAX(q.Dt) OVER (PARTITION BY q.QueueName) clnEndDate,
    STRING_AGG(s.SN,',')
FROM 
    @QH q
LEFT JOIN 
    @SNH s ON s.QueueName = q.QueueName AND s.Dt = q.Dt

as follows

Instead I get:

Msg 8120, Level 16, State 1, Line 36
Column '@QH.QueueName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Sorry, I can't get it.

@QH.QueueName isn't even mentioned in the SELECT, only q.QueueName.

What I am missing here?

CodePudding user response：

As per the docs, a group by clause is required if the string_agg is not the only item being selected. Once grouping correctly you no longer need window functions or distinct.

To obtain only distinct values of SN you need to group twice, the first time in a sub-query (in this case a CTE) to get distinct values of SN and the second time with string_agg to get distinct values of QueueName.

WITH cte AS (
    SELECT
        q.QueueName clnQueueName
        , MIN(q.Dt) clnStartDate
        , MAX(q.Dt) clnEndDate
        , s.SN
    FROM @QH q
    LEFT JOIN @SNH s
        ON s.QueueName = q.QueueName AND s.Dt = q.Dt
    GROUP BY q.QueueName, s.SN
)
SELECT clnQueueName
    , MIN(clnStartDate) clnStartDate
    , MAX(clnEndDate) clnEndDate
    , STRING_AGG(SN,',') SN
FROM cte
GROUP BY clnQueueName;

Returns: