Combine rows into one SQL Server

Question

My select query returns a list of 10 rows.

I tried use a COALESCE, but the exception throws "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

My query:

DECLARE @sqlDetails NVARCHAR(MAX);
SET @sqlDetails = COALESCE( @sqlDetails + ' ', '') + (  SELECT
                                                        rp.RuleDetails 
                                                    FROM 
                                                        RuleBook rb, 
                                                        RuleException re, 
                                                        RuleParameters rp 
                                                    WHERE 
                                                        rb.RuleBookID = 57 AND
                                                        rb.RuleBookID = re.RuleBookID AND 
                                                        rp.RuleBookID = re.RuleBookID AND
                                                        rp.RuleDetails NOT LIKE '%TRIGGER%' AND
                                                        rp.RuleDetails NOT LIKE '%EmailTo%');

I need concatenate the return into ONE single row, How I can do this?

Thanks.

what your select block gives you in result? (select rp.RuleDetails), it give single record OR Multiple? — Sandip - Frontend Developer
– Sandip - Frontend Developer, Commented Apr 28, 2017 at 12:25

Community · Accepted Answer · 2017-05-23 12:17:50Z

1

Try using STUFF AND FOR XML PATH see How Stuff and 'For Xml Path' work in Sql Server

SET @sqlDetails = STUFF((SELECT ','+RuleDetails FROM RuleBook rb, RuleException re, RuleParameters rp 
where rb.RuleBookID = 57 AND rb.RuleBookID = re.RuleBookID AND rp.RuleBookID = re.RuleBookID AND rp.RuleDetails NOT LIKE '%TRIGGER%' AND rp.RuleDetails NOT LIKE '%EmailTo%'FOR XML PATH('')) , 1 , 1 , '' )

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Apr 28, 2017 at 12:09

nelsonrr

111 bronze badge

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

Comments

DataWrangler · Accepted Answer · 2017-04-28 14:24:44Z

Hey Jose as you are trying the get the data into a variable, my assumption is that you need only a single record. As a Variable cannot store multiple values.

Please see if the below query helps your requirement.

DECLARE @sqlDetails NVARCHAR(MAX);
SET @sqlDetails = COALESCE( @sqlDetails + ' ', '') + (  SELECT
                                                        TOP 1 rp.RuleDetails 
                                                    FROM 
                                                        RuleBook rb, 
                                                        RuleException re, 
                                                        RuleParameters rp 
                                                    WHERE 
                                                        rb.RuleBookID = 57 AND
                                                        rb.RuleBookID = re.RuleBookID AND 
                                                        rp.RuleBookID = re.RuleBookID AND
                                                        rp.RuleDetails NOT LIKE '%TRIGGER%' AND
                                                        rp.RuleDetails NOT LIKE '%EmailTo%');

SELECT @sqlDetails

If your requirement is to pull all the records from the SubQuery and form a string separated by comma you can use the below query. This is similar to what Nelsonrr had mentioned previously

DECLARE @sqlDetails NVARCHAR(MAX);
SET @sqlDetails =(SELECT @sqlDetails ) + ',' + (SELECT STUFF((SELECT ','+
                                                        rp.RuleDetails 
                                                    FROM 
                                                        RuleBook rb, 
                                                        RuleException re, 
                                                        RuleParameters rp 
                                                    WHERE 
                                                        rb.RuleBookID = 57 AND
                                                        rb.RuleBookID = re.RuleBookID AND 
                                                        rp.RuleBookID = re.RuleBookID AND
                                                        rp.RuleDetails NOT LIKE '%TRIGGER%' AND
                                                        rp.RuleDetails NOT LIKE '%EmailTo%'FOR XML PATH('')) , 1 , 1 , ''));

SELECT @sqlDetails

Collectives™ on Stack Overflow

Combine rows into one SQL Server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related