creating a UDF for returning several rows into one row
Somebody posted this nice solution earlier:
create function ValList ( @anID VARCHAR(5) ) returns varchar(500) as
BEGIN
DECLARE @retval VARCHAR(500)
SET @retval = ''
SELECT @retVal = @retval + CASE WHEN @retVal = '' THEN '' ELSE ',' END + myVal
FROM TestMe (NOLOCK) WHERE myID = @anID
RETURN @retVal
END
select DISTINCT myID, dbo.ValList( myID )
from TestMe