Microsoft SQL Server 2012 多活跃月份年份分组查询需求

Since SQL Server 2012 doesn’t include the STRING_AGG function (introduced in 2017), we can use a combination of STUFF and FOR XML PATH to concatenate month values, paired with grouping and filtering to target only years with 2+ active months. Here’s a single, optimized query that delivers exactly what you need:

SELECT 
    [Year],
    STUFF(
        (SELECT ', ' + [Month] 
         FROM YourTableName 
         WHERE [Year] = t.[Year] AND Active = 'Y'
         FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 
        1, 2, ''
    ) AS [Month]
FROM YourTableName t
WHERE Active = 'Y'
GROUP BY [Year]
HAVING COUNT(*) >= 2;

Breakdown of how this works: #

• Grouping & Filtering: The outer query groups rows by Year and uses HAVING COUNT(*) >= 2 to retain only years with 2 or more entries where Active = 'Y'.
• Concatenation Logic: The subquery uses FOR XML PATH('') to stitch together matching Month values into a string prefixed with ', '. The STUFF function then removes the leading ', ' to produce a clean comma-separated list.
• Special Character Handling: Using TYPE and .value('.', 'NVARCHAR(MAX)') ensures any special characters in month names are handled correctly without XML encoding glitches.

Test Result with Your Sample Data: #

Running this query against your provided sample will output:

Just replace YourTableName with your actual table name, and you’re ready to go!

内容的提问来源于stack exchange，提问作者smul86