How to Make a List of T-SQL Results with Comma's Between Them

How to make a list of T-SQL results with comma's between them?

this will give you the list of values in a comma separated list

create table #temp
(
    y int,
    x varchar(10)
)

insert into #temp values (1, 'value 1')
insert into #temp values (1, 'value 2')
insert into #temp values (1, 'value 3')
insert into #temp values (1, 'value 4')

DECLARE @listStr varchar(255)

SELECT @listStr = COALESCE(@listStr+', ', '') + x
FROM #temp
WHERE #temp.y = 1

SELECT @listStr as List

drop table #temp

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('').

How do I create a comma-separated list using a SQL query?

There is no way to do it in a DB-agnostic way.
So you need to get the whole data-set like this:

select 
  r.name as ResName, 
  a.name as AppName
from 
  Resouces as r, 
  Applications as a, 
  ApplicationsResources as ar
where
  ar.app_id = a.id 
  and ar.resource_id = r.id

And then concat the AppName programmatically while grouping by ResName.

Convert multiple rows into one with comma as separator

This should work for you. Tested all the way back to SQL 2000.

create table #user (username varchar(25))

insert into #user (username) values ('Paul')
insert into #user (username) values ('John')
insert into #user (username) values ('Mary')

declare @tmp varchar(250)
SET @tmp = ''
select @tmp = @tmp + username + ', ' from #user

select SUBSTRING(@tmp, 0, LEN(@tmp))

Column into Array Or Comma Delimited List in t-sql

Here's another example of how you can do a comma delimited value. Will work in SQL Version 2008 R2 and up:

    DECLARE @TestData TABLE
        (
            [ListItem] NVARCHAR(100)
        );

    --Declared as blank since leaving NULL will result in a final NULL value.
    DECLARE @DelimitedString NVARCHAR(500) = '';

    INSERT INTO @TestData (
                              [ListItem]
                          )
    VALUES ( N'ListItem1' )
         , ( N'ListItem2' )
         , ( N'ListItem3' );

    --puts the result set into @DelimitedString
    SELECT @DelimitedString = @DelimitedString + [ListItem] + N','
    FROM   @TestData;

    --Remove trailing comma
    SET @DelimitedString = SUBSTRING(
                                        @DelimitedString
                                      , 1
                                      , LEN(@DelimitedString) - 1
                                    );
    SELECT @DelimitedString;

Multiple rows to one comma-separated value in Sql Server

Test Data

DECLARE @Table1 TABLE(ID INT, Value INT)
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400)

Query

SELECT  ID
       ,STUFF((SELECT ', ' + CAST(Value AS VARCHAR(10)) [text()]
         FROM @Table1 
         WHERE ID = t.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') List_Output
FROM @Table1 t
GROUP BY ID

Result Set

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝

SQL Server 2017 and Later Versions

If you are working on SQL Server 2017 or later versions, you can use built-in SQL Server Function STRING_AGG to create the comma delimited list:

DECLARE @Table1 TABLE(ID INT, Value INT);
INSERT INTO @Table1 VALUES (1,100),(1,200),(1,300),(1,400);

SELECT ID , STRING_AGG([Value], ', ') AS List_Output
FROM @Table1
GROUP BY ID;

Result Set

╔════╦═════════════════════╗
║ ID ║     List_Output     ║
╠════╬═════════════════════╣
║  1 ║  100, 200, 300, 400 ║
╚════╩═════════════════════╝

Concatenating Column Values into a Comma-Separated List

You can do a shortcut using coalesce to concatenate a series of strings from a record in a table, for example.

declare @aa varchar (200)
set @aa = ''

select @aa = 
    case when @aa = ''
    then CarName
    else @aa + coalesce(',' + CarName, '')
    end
  from Cars

print @aa

How do I use a comma separated list of values as a filter in T-SQL?

Try using Case, which serves the purpose of an IIF or a ternary operator. Please check this link http://msdn.microsoft.com/en-us/library/ms181765.aspx

cheers

---

Related Topics