SQL Server Sub Query with a Comma Separated Resultset

sql server sub query with a comma separated resultset

Here's a trick I've used in the past to do similar things. Use SUBSTRING function.

    SELECT n.nominationID
        , SUBSTRING((
                            SELECT ',' + naf.awardFocusName
                            FROM NominationAwardFocus naf
                            JOIN AwardFocus af
                                ON naf.awardFocusID = af.awardFocusID
                            WHERE n.nominationID = naf.nominationID
                            FOR XML PATH('')

                        ), 2, 1000000)
    FROM Nomination n

Note that the 2 is used to chop off the leading comma that the subselect adds to the first item, and 1000000 is chosen as a large number to mean "all of the rest of the string".

Combine multiple results in a subquery into a single comma-separated value

1. Create the UDF:

CREATE FUNCTION CombineValues
(
    @FK_ID INT -- The foreign key from TableA which is used 
               -- to fetch corresponding records
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SomeColumnList VARCHAR(8000);

SELECT @SomeColumnList =
    COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20)) 
FROM TableB C
WHERE C.FK_ID = @FK_ID;

RETURN 
(
    SELECT @SomeColumnList
)
END

2. Use in subquery:

SELECT ID, Name, dbo.CombineValues(FK_ID) FROM TableA

3. If you are using stored procedure you can do like this:

CREATE PROCEDURE GetCombinedValues
 @FK_ID int
As
BEGIN
DECLARE @SomeColumnList VARCHAR(800)
SELECT @SomeColumnList =
    COALESCE(@SomeColumnList + ', ', '') + CAST(SomeColumn AS varchar(20)) 
FROM TableB
WHERE FK_ID = @FK_ID 

Select *, @SomeColumnList as SelectedIds
    FROM 
        TableA
    WHERE 
        FK_ID = @FK_ID 
END

Can I get comma separated values from sub query? If not, how to get this done?

SELECT Country, AllStates = 
    STUFF((SELECT ', ' + State
           FROM Country_State_Mapping b 
           WHERE b.Country = a.Country
           FOR XML PATH('')), 1, 2, '')
FROM Country_State_Mapping a
GROUP BY Country

Create IN subquery from a VARCHAR parameter with comma separated values

WHERE CountryCode IN (SELECT *
                      FROM [dbo].[ufn_CSVToTable](@ExcludeCountries,',')

This is how you can create [dbo].[ufn_CSVToTable]:

How to convert comma separated NVARCHAR to table records in SQL Server 2005?

Converting comma-separated value to in subquery

One method is dynamic SQL:

declare @sql nvarchar(max);

set @sql = 'Select * from TableName Where Status in (@list)';

set @sql = replace(@sql, '@list', '@OrderStatuses');

exec sp_executesql @sql;

Note: You cannot pass a list in as a parameter.

You can also use like:

Select *
from TableName
Where ',' + @OrderStatuses + ',' like '%,' + Status + ',%';

However, this cannot use an index for the the comparison.

Comma separated results in SQL

Update (As suggested by @Aaron in the comment)

STRING_AGG is the preferred way of doing this in the modern versions of SQL Server (2017 or later). It also supports easy ordering.

SELECT
    STUDENTNUMBER
    , STRING_AGG(INSTITUTIONNAME, ', ') AS StringAggList
    , STRING_AGG(INSTITUTIONNAME, ', ') WITHIN GROUP (ORDER BY INSTITUTIONNAME DESC) AS StringAggListDesc
FROM Education E
GROUP BY E.STUDENTNUMBER;

Original Answer:

Use FOR XML PATH('') - which is converting the entries to a comma separated string and STUFF() -which is to trim the first comma- as follows Which gives you the same comma separated result

SELECT
    STUFF((SELECT ',' + INSTITUTIONNAME
              FROM EDUCATION EE
              WHERE  EE.STUDENTNUMBER = E.STUDENTNUMBER
              ORDER BY sortOrder
              FOR XML PATH(''), TYPE).value('text()[1]', 'nvarchar(max)')
        , 1, LEN(','), '') AS XmlPathList
FROM EDUCATION E
GROUP BY E.STUDENTNUMBER

Here is the FIDDLE showing results for both STRING_AGG and FOR XML PATH('').

Get comma-separated set of values from table where another reference value on another table appears twice (or more)

Nice starting fiddle, thanks! If we just take what you already have and put it in a CTE, we can write a standard string aggregation around it:

;WITH subs AS 
(
  SELECT prod.CODE, sub.SUBSTITUTECODE
  FROM @SUBSTITUTE AS sub
  INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID
  WHERE sub.SUBSTITUTECODE IN (SELECT sub.SUBSTITUTECODE
    FROM @SUBSTITUTE AS sub
    INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID
    GROUP BY sub.SUBSTITUTECODE
    HAVING COUNT(sub.SUBSTITUTECODE) > 1)
)
SELECT CODES = STUFF((SELECT ',' + CODE 
  FROM subs AS s2 WHERE s2.SUBSTITUTECODE = subs.SUBSTITUTECODE
  FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'),1,1,''),
    SUBSTITUTECODE FROM subs
  GROUP BY SUBSTITUTECODE;

• Example db<>fiddle

But we can simplify this code slightly, most importantly to avoid referencing both tables twice, like this:

;WITH subs AS
(
  SELECT s.ITEID, s.SUBSTITUTECODE, m.CODE, 
    c = COUNT(*) OVER (PARTITION BY s.SUBSTITUTECODE)
  FROM @SUBSTITUTE AS s
  INNER JOIN @MATERIAL AS m
  ON m.ID = s.ITEID
)
SELECT CODES = STUFF((SELECT ',' + CODE
  FROM subs AS s2 WHERE s2.SUBSTITUTECODE = subs.SUBSTITUTECODE
  FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'),1,1,''),
    SUBSTITUTECODE
  FROM subs 
  WHERE c > 1 
  GROUP BY SUBSTITUTECODE;

• Example db<>fiddle

Note that on more modern versions of SQL Server (2017+), STRING_AGG() makes this much easier:

SELECT CODES = STRING_AGG(m.CODE, ','), s.SUBSTITUTECODE
  FROM @SUBSTITUTE AS s
  INNER JOIN @MATERIAL AS m
  ON m.ID = s.ITEID
  GROUP BY s.SUBSTITUTECODE
  HAVING COUNT(*) > 1;

• Example db<>fiddle

---

Related Topics