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.

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;