Geek asks and answers

Select multiple rows in one variable in SQL Query

SELECT ft.ThreadTitle AS Title, 
fr.ReplyText + ' ' + ua2.Username + ' ' + ft.ThreadText + ' '  +

-- THIS NEXT LINE IS WHAT I WANT TO ACHIEVE:

(Select ReplyText from ForumReply Where ThreadID=ft.ThreadID) 

-- THE ABOVE LINE HAVE MULTIPLE ROWS/VALUES THAT I WANT TO JOIN INTO ONE VARIABLE. HOW?
AS [Content], 

ss.Domain, 
ss.SiteID, 
ft.ThreadID AS ObjectId
FROM         dbo.ForumReply AS fr INNER JOIN
                      dbo.ForumThreads AS ft ON fr.ThreadID = ft.ThreadID INNER JOIN
                      dbo.User_Account AS ua1 ON ft.CreateByUserID = ua1.UserID INNER JOIN
                      dbo.User_Account AS ua2 ON fr.ReplyUserID = ua2.UserID INNER JOIN
                      dbo.SysT_Site AS ss ON ua1.SiteID = ss.SiteID

This query gives an error: The subquery returned more than 1 value. This is unacceptable when a subquery follows = ,! =, <, <=,>,> = or when the subquery is used as an expression.

How can I rewrite this query to work to get all values ​​in one variable? The end result should be “View”, which looks like this:

.ObjectID int

.Content (a string with all the text that exists in Forumthread.threadText and forumReply.ReplyText)

.Domain string

.SiteID int

+4
source share
4 answers

Based on Martin's comment:

DECLARE @t TABLE  (id int, ReplyText varchar(100))
INSERT INTO @t (id, ReplyText) VALUES (1, 'So Long,')
INSERT INTO @t (id, ReplyText) VALUES  (2, 'And Thanks for')
INSERT INTO @t (id, ReplyText) VALUES  (3, 'All of the Fish!')

SELECT (SELECT replytext + ' '  FROM @t FOR XML PATH('')) AS CONTENT
+18
source

, concat aggregate, sql server unfortunuatley.

, .Net CLr, .

+1

You can assign a local variable this way.

SELECT @TempVariable = (SELECT replytext + ' ' FROM @t FOR XML PATH(''))
+1
source

I used STUFF:

SELECT
MyTable.Number AS Number,
(SELECT stuff(
    (
    select ', ' + x from (SELECT MyOtherTable.Name AS x FROM MyOtherTable WHERE MyOtherTable.ID = MyTable.ID) tb 
    FOR XML PATH('')
    )
, 1, 2, ''))AS AllNames

FROM
MyTable

Result:

Number:   AllNames:
  1       Andrew, Carl
  2       Bobby, Dave
+1
source

More articles:


All Articles