Select multiple rows into one variable in SQL Query

Question

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 error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How do I rewrite this query to work so that I get all the values into one variable? The end result need to be a "View" that looks like this:

.ObjectID int

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

.Domain string

.SiteID int

That is exactly what I don`t want. I want all recordsets and join them together into one value — Joakim
– Joakim, Commented Mar 10, 2011 at 16:25
You can concatenate values from multiple rows into a string in SQL Server using XML PATH — Martin Smith
– Martin Smith, Commented Mar 10, 2011 at 16:29

dyodji · Accepted Answer · 2021-02-12 00:22:47Z

24

Building upon 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 the Fish!')

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

edited Feb 12, 2021 at 0:22

dyodji

7,9142 gold badges18 silver badges17 bronze badges

answered Mar 10, 2011 at 16:52

jfollas

1,2357 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Valentino Vranken Over a year ago

Number 3 should be "all the fish" ;)

nhahtdh · Accepted Answer · 2013-04-30 11:47:47Z

2

You can assign to local variable this way.

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

edited Apr 30, 2013 at 11:47

nhahtdh

56.9k15 gold badges131 silver badges164 bronze badges

answered Apr 30, 2013 at 11:35

Khuzaima Shajapurwala

211 bronze badge

1 Comment

aads Over a year ago

@t is DECLARE @t TABLE (id int, ReplyText varchar(100))

Ace Indy · Accepted Answer · 2015-12-15 15:19:10Z

1

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

answered Dec 15, 2015 at 15:19

Ace Indy

1191 silver badge5 bronze badges

Comments

Richard Friend · Accepted Answer · 2011-03-10 16:28:45Z

0

What you need is a string concat aggregate function, which sql server unfortunuatley does not include.

You can create your own though using .Net CLr function see here and here

answered Mar 10, 2011 at 16:28

Richard Friend

16k1 gold badge45 silver badges60 bronze badges

Collectives™ on Stack Overflow

Select multiple rows into one variable in SQL Query

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related