Comma-separated query results for use by IN clause in SQL

Question

I wish to return a series of numerical results (i.e. those of a column) and make these comma separated. So the output can be used by the IN clause in another query. So I'm looking for 10,20,30,40,50,60 as output when the table data is as follows:

ID   Value
1    10
2    20
3    30
4    40
5    50
6    60

What is the smartest way to perform this? The following query will use this output string in the following way:

select * from Table where Value IN (10,20,30,40,50,60)

I've tried creating a variable that takes the output of the select, but I only get one value. i.e.

declare @Value_List varchar(799);
select @Value_List = Value from Table select @Value_List;

However I only return a single value, let alone a list.

The rationale is that this is a debug script, we have a series of checks that we need to go through manually, so Ideally want to be break down queries into single statements for ease of use and clarity.

Answer 1

This should concatenate

DECLARE @result VARCHAR(MAX)

SELECT @result = COALESCE(@result+' ,','')+Value FROM Table

SELECT @result

Answer 2

1) Assuming your list of values isn't addressed with a simpler approach like Lamak outlined (i.e. you don't really need a list at all), here is one way to do it using dynamic SQL:

-- Declare & get the list of values from a query.
DECLARE @values varchar(2000);
SET @values = '';
SELECT @values = @values + CAST(Value AS varchar(5)) + ','
FROM tbl_A; -- hopefully some WHERE criteria here to make this interesting

-- Trim the trailing comma.
SET @values = SUBSTRING(@values, 1, Len(@values) - 1)

---- DEBUG: Confirm the list of values.
--SELECT @values As 'Values'
--/*
--Values
-------------------------------------------------------------------------------
--10,20,30,40,50,60
--
--(1 row(s) affected)
--*/

-- Dynamically use the list of values in an IN clause.
DECLARE @sql as nvarchar(max);
SET @sql = 'SELECT Value FROM tbl_A WHERE Value IN (' + @values + ')';

EXEC sp_executesql @sql;
/*
Value
-----------
10
20
30
40
50
60

(6 row(s) affected)
*/

SQLBook.com explains this approach in greater depth.

2) However, a subquery would be smarter when you could alternatively use one to get the list of values in the IN clause - for example:

SELECT a.Value
FROM tbl_A a
WHERE
    a.Value IN
        (SELECT b.Value
         FROM tbl_B b
         /* hopefully some WHERE criteria here to make this interesting */);

3) And as a more direct alternative to comma-delimited lists, consider Table-Valued Parameters. They can be simple, easy to read, and elegant (per your comment on your question to Lamak) - i.e. smart.

Consider a stored proc that accepts a "list" of IDs as a TVP (i.e. set) of IDs instead:

/*
--------------------------------------------------------------------------------------
    IntTableType for int TVPs (i.e. "the TVP")
--------------------------------------------------------------------------------------
*/

CREATE TYPE [dbo].[IntTableType] AS TABLE
(
    Value int
)
GO


/*
--------------------------------------------------------------------------------------
    Gets a set of Foos by ID. (i.e. "the stored proc")
--------------------------------------------------------------------------------------
*/

CREATE PROCEDURE [dbo].[uspGetFoos]
    @FooIdTable dbo.IntTableType readonly
AS
BEGIN
    SET NOCOUNT ON;

    SELECT f.ID, f.Column1, f.Column2 -- etcetera
    FROM dbo.Foo f
    WHERE f.ID IN (SELECT fi.Value FROM @FooIdTable);

    ---- or --
    --
    --SELECT f.ID, f.Column1, f.Column2 -- etcetera
    --FROM dbo.Foo f
    --JOIN @FooIdTable fi ON fi.Value = f.ID;
END
GO

You could then get the "list" (i.e. set) of IDs in one query and use it in another as you described:

DECLARE @fooIds dbo.IntTableType;
INSERT INTO @fooIds (Value)
SELECT IntColumn
FROM dbo.Whatever
WHERE 1 = 1; -- whatever

EXEC dbo.uspGetFoos @FooIdTable = @fooIds;

Of course, you could also similarly employ table variables or temp tables for IDs (the former being restricted to a single stored proc, function, or batch, though); but TVPs give you a first-class way to make tabular (i.e. set) data an input argument.

Answer 3

Assuming your delimited values aren't linear like your example, you can create a function that returns a table from a delimited string. I Copied the function from here

CREATE FUNCTION [dbo].[func_Split] 
    (   
    @DelimitedString    varchar(8000),
    @Delimiter              varchar(100) 
    )
RETURNS @tblArray TABLE
    (
    ElementID   int IDENTITY(1,1),  -- Array index
    Element     varchar(1000)               -- Array element contents
    )
AS
BEGIN

    -- Local Variable Declarations
    -- ---------------------------
    DECLARE @Index      smallint,
                    @Start      smallint,
                    @DelSize    smallint

    SET @DelSize = LEN(@Delimiter)

    -- Loop through source string and add elements to destination table array
    -- ----------------------------------------------------------------------
    WHILE LEN(@DelimitedString) > 0
    BEGIN

        SET @Index = CHARINDEX(@Delimiter, @DelimitedString)

        IF @Index = 0
            BEGIN

                INSERT INTO
                    @tblArray 
                    (Element)
                VALUES
                    (LTRIM(RTRIM(@DelimitedString)))

                BREAK
            END
        ELSE
            BEGIN

                INSERT INTO
                    @tblArray 
                    (Element)
                VALUES
                    (LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))

                SET @Start = @Index + @DelSize
                SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)

            END
    END

    RETURN
END

And your select would be

select * 
from Table 
where Value IN (select Element from func_split('10,20,30,40,50,60', ','))

DEMO

Answer 4

To do what you want, you'll need to use dynamic SQL. But can you explain better why you want to do it like that?, seems much more simple to just do:

SELECT * 
FROM Table 
WHERE Value IN (SELECT Value FROM YourTable WHERE ID <= 6)