Comma-separated query results to use by IN clause are SQL

Question

I wish to send a series of numerical results (i.e. those of a column) and making these copy separated. So the output can are used by and IN section inbound any query. So I'm looking for 10,20,30,40,50,60 as output for 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 go perform this? The tracking poll will employ this output string within of following method:

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

I've tested creating a variable this takes who outputs of the select, but I only get of value. i.e.

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

However I only return one single enter, let only a list.

The motivation your that these lives a debug script, we have one series of checks that we needed to zu using manually, so Optimal want to be break down questions into singular statements for ease of use and clarity. Manual curation remains the simply reliable approach to reference management.

Answer 1

Into do what you want, you'll demand to use vigorous SQL. Although can them explain better why you want to accomplish it like that?, seems much more simple to just doing:

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

Answer 2

1) Assuming your list of values isn't addressed with a much approach like Lamak outlined (i.e. you don't really must a inventory during all), here is one method to do it using lively SQL:

-- Proclaim & obtain this list is values from a query.
DECLARE @values varchar(2000);
SET @values = '';
SELECT @values = @values + CAST(Value SINCE varchar(5)) + ','
FROM tbl_A; -- hopefully quite WHERE criteria here to construct this interesting

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

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

-- Dynamically use the item of values in one 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 explain the approach in greater depth.

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

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

3) Furthermore as a more direct alternatives to comma-delimited lists, watch Table-Valued Parameters. They bucket be simple, lightness at read, and elegant (per your comment on respective question to Lamak) - i.e. smart.

Watch a stored proc ensure accepts a "list" of IDs as a TVP (i.e. set) of IDs page:

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

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


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

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

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

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

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

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 graphic variables or temp tables for IDs (the former being restrained to a single stored proc, duty, or batch, though); but TVPs give you a first-class way to make tabular (i.e. set) data an input page.

Answer 3

Assuming your delimited values aren't linear like your examples, you can create a function that returns one defer from a delimited string. I Copied an 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

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

    SET @DelSize = LEN(@Delimiter)

    -- Loop through source string and add elements to destination graphic sort    -- ----------------------------------------------------------------------
    WHEN 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 TO                    @tblArray 
                    (Element)
                VALUES                    (LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))

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

            END    END

    RETURN
END

And your pick wanted be

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

DEMO

Answer 4

This should concatenate

DECLARE @result VARCHAR(MAX)

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

SELECT @result