March 20, 2012

Concatenate many Rows into one Row

Declare @FishSpeciesIDs varchar(MAX) = '12,14,15'
Declare @Names varchar(MAX)
SELECT @Names = COALESCE(@Names + ', ', '') + Comname FROM dbo.FishSpecies WHERE ID IN (SELECT item FROM dbo.Split(@FishSpeciesIDs,','))
Select @Names

DEClARE @GearIDs varchar(MAX) = '12,14,15,16,17,18'
DECLARE @Names varchar(MAX)
SELECT @Names = COALESCE(@Names + ', ', '') + Description FROM ARDB.GearType WHERE ID IN (SELECT item FROM dbo.Split(@GearIDs,','))
SELECT @Names