Help needed with SQL Server query: Accessing columns not in GROUP BY clause

Question

I have a table whose columns are as follows:

CREATE TABLE Tests (
 TestDate date,
 EntityID int)

Using a stored procedure, I want to find the ‘academic year’ of pairs of tests that happened in the same ‘academic year’, where ‘academic year’ is defined as year part of the test date after a fixed number of months (@AcYearOff, value is typically equal to 7) is subtracted. I do this using the following code:

CREATE TABLE #tmpTable (
 vYear char(4),
 vDate char(8))
INSERT # tmpTable (vYear, vDate)
SELECT DATEPART(yyyy, dateadd(MONTH, @AcYearOff, TestDate)), format(TestDate, 'MMM yyyy')
FROM Tests
WHERE Tests.EntityID = @ EntityID
SELECT vYear
FROM # tmpTable
GROUP BY vYear
HAVING count(vYear) = 2
ORDER BY vYear

The above code functions exactly as expected. I now want to add to this query a string that shows the actual month and year in which the two tests were taken. I added the column vDate to the temporary table for this purpose, but I cannot figure out how to combine the two vDate values and return them as a string.

To be clear, here is some example data from the Tests table:

2018年08月12日, 21
2018年09月04日, 22
2019年04月17日, 21
2019年08月03日, 21

and the stored procedure should return:

‘2018’, ‘Aug 2018 and Apr 2019’

I look forward to seeing your suggestions. Thank you.

• Aaron Bertrand: What version of SQL Server please? Also, why type yyyy instead of YEAR?
• GRoston: version: SQL 2014 Why yyyy: Typo (but for some reason, it seems to have worked...)

Answer 1

In SQL Server 2017 or Azure SQL Database:

SELECT vYear, STRING_AGG(vDate, ' and ')
FROM #tmpTable
GROUP BY vYear
HAVING count(vYear) = 2
ORDER BY vYear;

In SQL Server 2016 or lower:

SELECT vYear, STUFF((SELECT ' and ' + vDate
 FROM #tmpTable
 WHERE vYear = t.vYear
 ORDER BY vDate
 FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,5,'')
FROM #tmpTable AS t
GROUP BY vYear
HAVING count(vYear) = 2
ORDER BY vYear;

• GRoston: Aaron - Would not have hit upon this in a million years. Tried your code and it pulled vDate for every entry that matched vYear. (Note that the real data has more entries that what was shown above.) Needless to say, simply inserting the GROUP BY and HAVING clauses into the inner SELECT does not work since vDate is not a part of those clauses.
• GRoston: Aaron - to be clearer: The real model is slightly more complex - there are two other columns that are used in the GROUP BY clause that I omitted from the example for simplicity. Thus, with this solution, the vDate for those entries get pulled in when vYear matches.
• GRoston: DUH! Just added those columns to the WHERE clause in the subquery. Works like a charm - thanks!
• Aaron Bertrand: @GRoston Yeah, sorry, I didn't include your WHERE clause, I just dumped the sample data from the question into the #temp tables, so I didn't have to apply any further filters.