Join a few strings

[JendaPerl]

Why the heck doesn't the agreggate function list contain something like
SELECT ..., Join( VarcharColumnName, ', ') FROM ...
and is there any way to create such a function? Any way!

I can split a string using a function into something that can be used as "... Foo in (SELECT Id FROM dbo.SplitList @List, ',')" but is there a way to merge the found values again?

What I'd need is to be able to write something like

...
Value = (SELECT StringJoin(MapTo, ',') FROM Mapping WHERE MapFrom in (SELECT Foo FROM dbo.Split( Value, ',')))
...

inside a rather complex query, is there a way?

Thanks, Jenda
(P.S.: Why does everyone go crazy with adding objects into relational datbases and noone cares to add just a bit of functional features. Give me foldl and unnamed functions and I can create virtually any aggregate function I like!)

[skhanal]

You can concatenate string in SELECT with a variable

declare @a varchar(8000)

set @a=''

select @a=@a+Mapto+','
FROM Mapping WHERE MapFrom in (SELECT Foo FROM dbo.Split( Value, ',')))

You will have a , at the end, that you can clean up with substring function.

[JendaPerl]

You can concatenate string in SELECT with a variable

declare @a varchar(8000)
set @a=''

select @a=@a+Mapto+','
FROM Mapping WHERE MapFrom in (SELECT Foo FROM dbo.Split( Value, ',')))

Sweeet. I did not know I can do this. Here's the function I ended up with :

CREATE FUNCTION dbo.SplitCharList(
    @Options varchar(8000),
    @Delim varchar(5)
) RETURNS @tbl TABLE(Id varchar(400) PRIMARY KEY)
as
BEGIN
	IF (@options is not NULL) BEGIN
	    Declare @idx int, @Id varchar(400);
	    SET @idx = CHARINDEX( @Delim, @options)
		IF (@idx = 1) BEGIN
	        SET @options = substring( @options, @idx + 1, 8000);
	        SET @idx = CHARINDEX( @Delim, @options);
		END
	    WHILE (@idx > 0) BEGIN
			SET @Id = substring( @options, 1, @idx - 1)
			IF not exists (SELECT * FROM @tbl WHERE Id = @Id)
		        INSERT INTO @tbl (
				 id
				) VALUES (
				 @Id
				);
	        SET @options = substring( @options, @idx + 1, 8000)
	        SET @idx = CHARINDEX( @Delim, @options)
	    END
		IF (@Options <> '') BEGIN
			SET @Id = @Options
			IF not exists (SELECT * FROM @tbl WHERE Id = @Id)
		        INSERT INTO @tbl (
				 id
				) VALUES (
				 @Id
				);
		END
	END

    RETURN
END
go

CREATE FUNCTION dbo.MapATSValue(
	@ATSFieldSettingId int,
    @Options varchar(8000)
) RETURNS varchar(8000)
as
BEGIN
	IF (@options is not NULL) BEGIN
		IF (CHARINDEX( char(2), @Options) = 0)
			-- single value
			SELECT @Options = ViperValue
			  FROM dbo.ATSFieldMapping WITH (NOLOCK)
			 WHERE ATSFieldSettingId = @ATSFieldSettingId
			   and ATSValue = @Options
		ELSE BEGIN
			Declare @Res varchar(8000), @Delim varchar(5);
			SET @Res = '';
			SET @Delim = char(2);
			SELECT @Res = @Res + ViperValue + @Delim
			  FROM dbo.ATSFieldMapping WITH (NOLOCK)
			  JOIN dbo.SplitCharList( @Options, @Delim) as List ON List.Id = ATSValue
			 WHERE ATSFieldSettingId = @ATSFieldSettingId

			SET @Options = substring( @Res, 1, len(@Res) - 1)
		END
	END

    RETURN (@Options);
END

Just in case it was of interest to anyone :-)