Retrieving multiple values from within a SQL Query?

I have a Stored Procedure that has a main query. To effectively accomplish what I want, I'm going to need a separate query along with the main query within the same SP. This query will retrieve a single column from one DataTable.

What I need to do is create a variable that will concatenate the values from the column retrieved from this query, with each value separated by a comma. This variable will be a VARCHAR. This variable will be inserted into the SELECT clause of the main query (which I know how to do). How can I accomplish this?

Thanks!

Microsoft SQL Server 2008

Last Comment

BlakeMcKenna

8/22/2022 - Mon

tibba

3/25/2015

Use a Common Table Expression (CTE)

;WITH CTE AS
    (
    SELECT  RowID, Txt
    FROM    YourTable
    WHERE  RowID = 1
    UNION ALL
    SELECT  YourTable.RowID, CTE.Txt + ',' + YourTable.Txt
    FROM    CTE
    JOIN    YourTable ON YourTable. RowID = CTE.RowID + 1
    )
    SELECT  Txt
    FROM    CTE
    WHERE  RowID = (SELECT MAX(RowID) FROM YourTable)

Open in new window

or Using a WHILE loop or Cursor

DECLARE @MaxCount INTEGER
DECLARE @Count INTEGER
DECLARE @Txt VARCHAR(MAX)
SET @Count = 1
SET @Txt = ''
SET @MaxCount = (SELECT MAX(RowID) FROM YourTable) 
WHILE @Count<=@MaxCount
    BEGIN
    IF @Txt!=''
        SET @Txt=@Txt+',' + (SELECT Txt FROM YourTable WHERE RowID=@Count)
    ELSE
        SET @Txt=(SELECT Txt FROM YourTable WHERE RowID=@Count)
    SET @Count=@Count+1
    END
SELECT @Txt AS Txt

Open in new window

Éric Moreau

3/25/2015

You want to concatenate values from a SQL query group by something. Right? In this case, have a look at http://emoreau.com/Entries/Blogs/2009/10/SQL-Concatenate-values-multiple-from-multiple-lines.aspx

Carl Tawn

3/26/2015

If you just want to concatenate the values from a single column, without any grouping, you can do something like:

declare @output varchar(max);
set @output = ''

select @output = @output + [word] + ',' 
	from dbo.Test

print substring(@output, 1, len(@output)-1)

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

BlakeMcKenna

3/26/2015

ASKER

Carl,

I tried what you suggested and it didn't work. See attached Image.
Capture.JPG

ASKER CERTIFIED SOLUTION

Carl Tawn

3/26/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

BlakeMcKenna

3/26/2015

ASKER

Sorry, I forgot to mention that...it is a numeric value and your last post worked great!

Thanks!