How to get comma separated values in a single select statement

Sometimes you want to get comma separated values from another table in the same select statement, for this you can use the following approaches

–Approach 1

SELECT SUBSTRING(((SELECT (‘, ‘ + Aud_Type)

FROM   Audience_Type

WHERE  Id_Aud_Type IN (SELECT value

FROM   dbo.fn_Split (‘1,2,3’, ‘,’))

FOR    XML PATH (”))), 3, 1000)

FROM   Request;

GO

–Approach 2

SELECT SUBSTRING(((SELECT (‘, ‘ + Aud_Type)

FROM   Audience_Type AS t2

WHERE  t2.RequestID = req.RequestID

FOR    XML PATH (”))), 3, 1000) AS Aud_Type

FROM   SOP_Request AS req;

GO

You just have to replace the table/column names (Aud_Type, Audience_Type, Id_Aud_Type, SOP_Request) with your ones

–Split Function  which is being used in approach 1

CREATE FUNCTION [dbo].[fn_Split]

(@sText VARCHAR (8000), @sDelim VARCHAR (20)=’ ‘)

RETURNS

@retArray TABLE (

idx   SMALLINT       PRIMARY KEY,

value VARCHAR (8000))

AS

BEGIN

DECLARE @idx AS SMALLINT,

@value AS VARCHAR (8000),

@bcontinue AS BIT,

@iStrike AS SMALLINT,

@iDelimlength AS TINYINT;

IF @sDelim = ‘Space’

BEGIN

SET @sDelim = ‘ ‘;

END

SET @idx = 0;

SET @sText = LTrim(RTrim(@sText));

SET @iDelimlength = DATALENGTH(@sDelim);

SET @bcontinue = 1;

IF NOT ((@iDelimlength = 0)

OR (@sDelim = ‘Empty’))

BEGIN

WHILE @bcontinue = 1

BEGIN

–If you can find the delimiter in the text, retrieve the first element and

–insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText) > 0

BEGIN

SET @value = SUBSTRING(@sText, 1, CHARINDEX(@sDelim, @sText) – 1);

BEGIN

INSERT  @retArray (idx, value)

VALUES           (@idx, @value);

END

–Trim the element and its delimiter from the front of the string.

–Increment the index and loop.

SET @iStrike = DATALENGTH(@value) + @iDelimlength;

SET @idx = @idx + 1;

SET @sText = LTrim(RIGHT(@sText, DATALENGTH(@sText) – @iStrike));

END

ELSE

BEGIN

–If you can’t find the delimiter in the text, @sText is the last value in

–@retArray.

SET @value = @sText;

BEGIN

INSERT  @retArray (idx, value)

VALUES           (@idx, @value);

END

–Exit the WHILE loop.

SET @bcontinue = 0;

END

END

END

ELSE

BEGIN

WHILE @bcontinue = 1

BEGIN

–If the delimiter is an empty string, check for remaining text

–instead of a delimiter. Insert the first character into the

–retArray table. Trim the character from the front of the string.

–Increment the index and loop.

IF DATALENGTH(@sText) > 1

BEGIN

SET @value = SUBSTRING(@sText, 1, 1);

BEGIN

INSERT  @retArray (idx, value)

VALUES           (@idx, @value);

END

SET @idx = @idx + 1;

SET @sText = SUBSTRING(@sText, 2, DATALENGTH(@sText) – 1);

END

ELSE

BEGIN

–One character remains.

–Insert the character, and exit the WHILE loop.

INSERT  @retArray (idx, value)

VALUES           (@idx, @sText);

SET @bcontinue = 0;

END

END

END

RETURN;

END

–ATLTUsers.eoEmployingorganization_ID in (SELECT value from dbo.fn_Split(”’+@EOID+ ”’, ”,”))’

Enjoy

Facebook Twitter Email

Leave a Reply

Your email address will not be published. Required fields are marked *