SQL Syntax - Combining two rows into one variable

WaldaInc
WaldaInc used Ask the Experts™
on
Hi.

I have the SQL statement below

SELECT COALESCE(Cast(@ID as Varchar(10)) + ',', '') + Cast(ImproperID as Varchar(10))
FROM ImproperReason
WHERE ImproperCode = @Code

Which works fine, it returns two rows of data.  However I want to assign this to a variable that is comma delimted and when I do this

DECLARE @myVar varchar(max)
SET @myVar = (SELECT COALESCE(Cast(@ID as Varchar(10)) + ',', '') + Cast(ImproperID as Varchar(10))
FROM ImproperReason
WHERE ImproperCode = @Code)

I get an error about subquery returning more than one value.

How do I get the two rows returned to be one variable, comma delimted such as:

1,2
or
1,52
etc...

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I don't know MS SQL . I am a user of DB2 and oracle. But it seems the correct syntax for cursor is
DECLARE @MyVariable CURSOR

SET @MyVariable = CURSOR SCROLL KEYSET FOR
SELECT LastName FROM Northwind.dbo.Employees

OR
DECLARE @MyVariable CURSOR
DECLARE MyCursor CURSOR FOR
SELECT LastName FROM Northwind.dbo.Employees

SET @MyVariable = MyCursor

Other example
DECLARE @VAR1 INT
DECLARE @VAR2 VARCHAR(20)
DECLARE @VAR3 VARCHAR(20)
DECLARE @VAR4 INT
DECLARE @VAR5 INT
DECLARE @CUR CURSOR

SET @CUR = CURSOR FOR
SELECT DISTINCT SNO , TYPE FROM TRANTABLES
OPEN @CUR
FETCH NEXT
FROM @CUR INTO @VAR1 , @VAR2
WHILE @@FETCH_STATUS = 0
BEGIN

Database Consultant
Top Expert 2009
Commented:
SELECT @myVar = COALESCE(@myVar +'','' ) +COALESCE(Cast(@ID as Varchar(10)) + ',', '') + Cast(ImproperID as Varchar(10))
FROM ImproperReason
WHERE ImproperCode = @Code
Top Expert 2010

Commented:
There is probably no need to do a cursor here.

Can you work up a simple example of what the query returns now, and what you want the end result to be?
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Commented:
I"m confused. Your working sql returns two rows of two columns each, in other words, 4 values in all.  But you seem to only want a variable containing two values, comma delimited.

But, let's say you just care about the ImproperId values, then do like this:

DECLARE @myVar varchar(max)
SET @myVar = NULL
SELECT @myVar = COALESCE(@MyVar + ',', '') + Cast(ImproperID as Varchar(10))
FROM ImproperReason
WHERE ImproperCode = @Code)




DECLARE @myVar varchar(max)
SELECT @myVar = ',' + Cast(@ID as Varchar(10))  
FROM ImproperReason
WHERE ImproperCode = @Code
FOR XML PATH ('')


   

SharathData Engineer

Commented:
try this
;WITH cte 
     AS (SELECT COALESCE(CAST(@ID AS VARCHAR(10)) + ',','') + CAST(ImproperID AS VARCHAR(10)) ImproperID 
         FROM   ImproperReason 
         WHERE  ImproperCode = @Code) 
SELECT RTRIM(SUBSTRING(ISNULL((SELECT ',' + ImproperID 
                               FROM   cte 
                               for xml path('')),' '),2,2000)) AS ImproperID

Open in new window

Author

Commented:
My syntax was messed up.

Thanks for everyone's reply.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial