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, Apr 28 '17 at 12:25

score 1 · Answer 1 · edited May 23 '17 at 12:17

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 , '' )

DataWrangler · Answer 2 · 2017-04-28T14:24:44.840

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

Combine rows into one SQL Server

2 Answers2