Wednesday, July 20, 2011

Concatenate values in a SELECT statement

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
BEGIN
  SET @technicianNames = Left(@technicianNames, Len(@technicianNames )-1)
END

Say you had the following values in the Technican table:

TechnicianName
--------------
Dave
Trevor
Agnes

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!

No comments:

Post a Comment