Link to home
Create AccountLog in
Avatar of BlakeMcKenna
BlakeMcKennaFlag for United States of America

asked on

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!
Avatar of tibba
tibba
Flag of Australia image

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

Avatar of Éric Moreau
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
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

Avatar of BlakeMcKenna

ASKER

Carl,

I tried what you suggested and it didn't work. See attached Image.
Capture.JPG
ASKER CERTIFIED SOLUTION
Avatar of Carl Tawn
Carl Tawn
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Sorry, I forgot to mention that...it is a numeric value and your last post worked great!

Thanks!