March 27, 2008 at 8:06 pm
Hello all,
I have a query. In this query i am using a subquery to populate a column. This subquery is returning 2 records and i get "Subquery returned more than 1 value." error which i understand.
Is there a way to merge these 2 records using comma in between them without using a temporary table. the main query is already very complex and i can't get the result using join so i was trying to get the value and push them in a separate field.
For ex.
my subquery returns
A
B
i wan't it to return A,B that i can store in a separate field.
Any suggestions will be appreciated.
Thanks
March 27, 2008 at 8:34 pm
You can use XML to concatenate the results from the subquery. All you have to do is structure your subquery to look like the following.
You should keep in mind that a subquery gets evaluated for each row and using this method over lots of records can really bring down performance. There are lots of really good examples of how to concatenate row data into delimited strings using functions.
declare @t table(
MyColumn Varchar(1)
)
insert into @t
select 'a' union
select 'b'
select case when row_number() over(order by MyColumn) = 1 then
MyColumn
else
',' + MyColumn
end
from @t
for xml path('')
March 27, 2008 at 9:36 pm
I'm not to good with the XML tricks myself. Here's another way to do it:
Instead of:
[font="Courier New"](Select Col1 from Table1 Where {conditions}) as Col_A[/font]
Try:
[font="Courier New"](Select Min(Col1) from Table1 Where {conditions}) as Col_A,
(Select Max(Col1) from Table1 Where {conditions}) as Col_B[/font]
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2008 at 12:00 pm
I have a feeling you are using an '=' in your camparison to the subquery. Try using an 'IN' instead. This way it will compare the left side of the equation to all the values returned by your subquery on the right side of the equation.
ex.
WHERE Name = (SELECT Name FROM Address where NAME LIKE 'A%')
this will return the error you are getting.
instead try this:
WHERE Name IN (SELECT Name FROM Address WHERE NAME LIKE 'A%')
Dave Novak
March 28, 2008 at 1:52 pm
Thank you guys for your suggestions i ended up bringing that subquery out in a separate view and then did a join on it.
March 28, 2008 at 2:30 pm
Hi Adam,
The solution you provided works great on SQL2005 which is what this forum is for. Can we use something similar for SQL 2000.
Thanks
March 31, 2008 at 4:54 pm
Unfortunately, you cannot use the XML method or row number method in 2000. You should use a simple select statement to concatenate the string from you.
SET NOCOUNT ON
GO
create table #t(
col varchar(1)
)
insert into #t
select 'a' union
select 'b' union
select 'c' union
select 'd'
declare @Return varchar(8000)
SELECT @Return = ISNULL(@Return+',','')+ col
FROM #t
--WHERE SOMETHING
PRINT @Return
DROP TABLE #t
SET NOCOUNT OFF
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply