concatenate results from subquery into a single value

Question

I have a stored procedure that I'm building a report off of, and I came across a small issue I hope to get resolved.

The stored procedure only takes a single parameter:

@nID varchar(max)=null

This parameter can either be NULL, have 1 value = '1' or multiple values '1,3'

I have a split function in place, so a parameter that might look like this: '1,3,5' would turn it into '1','3','5'.

This is part of my select statement (subquery)

Select Value1, 
(select tblIns.nDesc from tblIns where tblIns.nID in (SELECT items FROM 
dbo.SplitFunction(@nID, ',', 'nID'))) as 'Description'
From ....
Where ....

The query works perfectly fine as long as I have only 1 value passed in my parameter @nID , however, when I pass more than one, I get the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What my goal is, to be able to concatenate the subquery result, so that if I pass in more than 1 value, the 'Description' would look something like this

 Text1, Text2, Text2

Is something like that doable? Or would this involve a lot more work.

Without the subquery, I can pass any number of values into the SP and it yields the correct results, I just need to get the TEXT description for the parameters and have them all be concatenated in one column.

EDIT clarification:

tblIns

  nID       nDesc
  1         Text1
  2         Text2

Let's say I pass values '1,2' as my parameter into the Stored Procedure, the results I'm hoping to get:

 Value1     Description
 Test       Text1, Text2

Make it simpler. Create table, populate with values. Show expected results. Otherwise I'm lost. — Slava Murygin, May 04 '18 at 18:35
Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Tab Alleman, May 04 '18 at 18:38

Brad · Accepted Answer · 2018-05-04T19:32:22.010

3

To do it into a variable this will put single qutes around each value and a comma seperator:

DECLARE @OUTPUT as varchar(MAX)
Set @OUTPUT = ''

SELECT @OUTPUT = @OUTPUT + case when @OUTPUT = '' then '' else ', '  end + '''' + rtrim(TableValue) + ''''
    FROM dbo.Table

select @OUTPUT

-- or you can do it inline like this:

Select STUFF((
    SELECT ',' + TableValue    
    FROM dbo.TableLookup
    FOR XML PATH('')), 1, 1,'') 
FROM dbo.TableLookup

edited May 04 '18 at 19:32

answered May 04 '18 at 18:31

Brad

3,454
3
27
50

Added note to code to update the 500 to what would be expected – Brad May 04 '18 at 18:38
I updated code to use STUFF instead of the substring, try the new code in my answer above – Brad May 04 '18 at 19:27

concatenate results from subquery into a single value

1 Answers1