I was digging through some old (very old) notes I had on SQL Server 7.0 and came across this one and thought I'd post it for my own reference... (updated a bit).
To concatenate the values of a particular column in a SELECT statement, do something like this:
DECLARE @technicianNames VARCHAR(max)
SET @technicianNames = ''
SELECT @technicianNames = @technicianNames + t.TechnicianName + ','
FROM dbo.Technician t
ORDER BY t.TechnicianName ASC
IF Len(@technicianNames) > 0
SET @technicianNames = Left(@technicianNames, Len(@technicianNames )-1)
Say you had the following values in the Technican table:
The value of @technicianNames would be "Dave,Trevor,Agnes".
Hopefully it's useful to someone else too... There may well be a better way to do this in the post SQL Server 7.0 world, if there is, please let me know!