What This Query Does to Create Comma Delimited List SQL Server

What this query does to create comma delimited list SQL Server?

The simplest way of explaining it is to look at how FOR XML PATH works for actual XML. Imagine a simple table Employee:

EmployeeID      Name
1               John Smith
2               Jane Doe

You could use

SELECT  EmployeeID, Name
FROM    emp.Employee
FOR XML PATH ('Employee')

This would create XML as follows

<Employee>
    <EmployeeID>1</EmployeeID>
    <Name>John Smith</Name>
</Employee>
<Employee>
    <EmployeeID>2</EmployeeID>
    <Name>Jane Doe</Name>
</Employee>

Removing the 'Employee' from PATH removes the outer xml tags so this query:

SELECT  Name
FROM    Employee
FOR XML PATH ('')

Would create

    <Name>John Smith</Name>
    <Name>Jane Doe</Name>

What you are then doing is not ideal, the column name 'data()' forces an sql error because it is trying to create an xml tag which is not a legal tag, so the following error is generated:

Column name 'Data()' contains an invalid XML identifier as required by FOR XML; '('(0x0028) is the first character at fault.

The correlated subquery hides this error and just generates the XML with no tags:

SELECT  Name AS [Data()]
FROM    Employee
FOR XML PATH ('')

creates

John Smith Jane Doe

You are then replacing spaces with commas, fairly self explanatory...

If I were you I would adapt the query slightly:

SELECT  E1.deptno, 
        STUFF(( SELECT  ', ' + E2.ename 
                FROM    emp AS e2 
                WHERE   e1.deptno = e2.DEPTNO 
                FOR XML PATH('')
            ), 1, 2, '') 
FROM    EMP AS e1 
GROUP BY DEPTNO; 

Having no column alias will mean no xml tags are created, and adding the comma within the select query means any names with spaces in will not cause errors,STUFF will remove the first comma and space.

ADDENDUM

To elaborate on what KM has said in a comment, as this seems to be getting a few more views, the correct way to escape XML characters would be to use .value as follows:

SELECT  E1.deptno, 
        STUFF(( SELECT  ', ' + E2.ename 
                FROM    emp AS e2 
                WHERE   e1.deptno = e2.DEPTNO 
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
FROM    EMP AS e1 
GROUP BY DEPTNO; 

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.

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 listStr

FROM EDUCATION E
GROUP BY E.STUDENTNUMBER

Here is the FIDDLE

How to create a comma delimited list using SELECT statement from table columns

Use STUFF and FOR XML PATH with correlated subquery:

select 
    class_id,
    stuff(
        (select ', ' + Instructor_Name from your_table b 
         where a.class_id = b.class_id for xml path('')),
        1, 2, ''
    ) names
from your_table a
group by
    class_id;

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.

comma delimited list as a single string, T-SQL

You could use FOR XML PATH and STUFF to concatenate the multiple rows into a single row:

select distinct t1.id,
  STUFF(
         (SELECT ', ' + convert(varchar(10), t2.date, 120)
          FROM yourtable t2
          where t1.id = t2.id
          FOR XML PATH (''))
          , 1, 1, '')  AS date
from yourtable t1;

See SQL Fiddle with Demo

SQL comma delimited list as a single string

Here's what I did to resolve this issue with my query to create a comma delimited list on a single string or in a single column. I had to write the code in a CTE with a substring using the XML Path. It works for what I need and I'll use the code within an SSRS report.

 ;with t1 as
(
SELECT b.relatedfirmaccountid, b.basefirmaccountid, c.firmaccountid, a.acctnbr as baseacctnbr, a1.acctnbr as relatedacct,
rn = row_number() over (partition by a.acctnbr order by a.acctnbr),
    substring((select ', '+c.acctnbr as 'data()' 
FROM [USBI].[vw_FirmAccount] a
    inner join [USBI].[vw_RelatedAccount] b on a.firmaccountid = b.basefirmaccountid 
    inner join [USBI].[vw_FirmAccount] a1 on a1.firmaccountid = b.relatedfirmaccountid
    inner join USBI.vw_NameAddressBase c on b.relatedfirmaccountid = c.firmaccountid
where a.acctnbr = '11727765'
    and c.restrdate <> '99999999'
    and c.closerestrictind <> 'c'
    and c.iscurrent = '1'
    and b.iscurrent = '1'
for xml path('')),2,255) as "AllRelatedAccts"

FROM [USBI].[vw_FirmAccount] a
    inner join [USBI].[vw_RelatedAccount] b on a.firmaccountid = b.basefirmaccountid 
    inner join [USBI].[vw_FirmAccount] a1 on a1.firmaccountid = b.relatedfirmaccountid
    inner join USBI.vw_NameAddressBase c on b.relatedfirmaccountid = c.firmaccountid
where a.acctnbr = '11727765'
    and c.restrdate <> '99999999'
    and c.closerestrictind <> 'c'
    and c.iscurrent = '1'
    and b.iscurrent = '1'
 ),
 t2 as
 (
select distinct a.acctnbr, a.name
from USBI.vw_NameAddressBase a
where a.acctnbr = '11727765'
    and a.restrdate <> '99999999'
    and a.closerestrictind <> 'c'
    and a.iscurrent = '1'
group by a.acctnbr, a.name
  )
select 
    a.baseacctnbr,
    a.relatedacct,
    a.allrelatedaccts,
    a.rn
from t1 a
inner join t2 b on a.baseacctnbr = b.acctnbr
--where rn = 1

My Results:

My Results after changing my where rn = 1

sql comma delimited list of a column in analytical function

Just for completeness. Remove the # symbols for your actual solution.

SET NOCOUNT ON;

CREATE TABLE #users
(
    Id INT,
    Name VARCHAR(32)
);

INSERT #users VALUES
(1,'John'),
(2,'Anton'),
(3,'Craig');

CREATE TABLE #products
(
    Id INT,
    UserId INT,
    Name VARCHAR(32),
    RecordCreateDate DATE,
    Value INT
);

INSERT #products VALUES
(1,1,'a','2012-12-21',10),
(2,1,'b','2012-12-11',20),
(3,1,'c','2012-12-01',30),
(4,2,'e','2012-12-05',40),
(5,2,'f','2012-12-17',50),
(6,3,'d','2012-12-21',60), 
(7,3,'i','2012-12-31',70);

The query:

;WITH x AS 
(
    SELECT UserId, Value, 
        row_num = ROW_NUMBER() OVER 
        (
              PARTITION BY UserId 
              ORDER BY RecordCreateDate
        )
        FROM #products
)
SELECT
  x.UserId,
  u.Name,
  ProductList = STUFF((
     SELECT ',' + Name
        FROM #Products AS p 
        WHERE p.UserId = x.UserId 
        FOR XML PATH(''), 
        TYPE).value(N'./text()[1]', N'varchar(max)'),1,1,''),
  x.Value
FROM x
INNER JOIN #users AS u
ON x.UserId = u.Id
WHERE x.row_num = 1;

Then clean up:

DROP TABLE #users, #products;

Results:

UserId  Name    ProductList  Value
1       John    a,b,c        30
2       Anton   e,f          40
3       Craig   d,i          60

SQL Server - join rows into comma separated list

You are missing the condition inside the sub query.

SELECT t2.Id, STUFF((SELECT ',' + CAST(VALUE AS varchar) FROM @MyTable t1  where t1.Id =t2.ID FOR XML PATH('')), 1 ,1, '') AS ValueList
FROM @MyTable t2
GROUP BY t2.Id

Demo

SQL data returned in a comma-delimited list

Since this is SQL 2000 you options are somewhat limited. You can't use FOR XML PATH or recursive CTEs

Also the COALESCE trick only works for stuffing a comma delimited list for single hotel. Not all of them.

Honestly you're better off doing this in the client. But if that can't be done (certain Report Apps that-which-must-not-be-named come to mind) you can do the following.

Note the number of times the loop process is equal to maximum number of files associated with a hotel. This is much better than setting up a loop for each hotel (e.g. calling a UDF from the select clause).

DECLARE @foo table ( HotelID int , lastID int , FileNameList varchar(8000))

INSERT INTO @Foo 

SELECT
    start.HotelID,
    start.firstID ,
    id.FileName 

FROM
    (SELECT 
        HotelID,
        min(HotelImageID ) firstID
    from 
        HotelImages


    group by HotelID) start
    INNER JOIN HotelImages hi
    ON start.firstID = hi.HotelImageID 


WHILE @@RowCount <> 0 
BEGIN

    UPDATE @foo
    SET 
        FileNameList = FileNameList + ',' + FileName
        lastID  = Xnext.nextID
    FROM 
        @foo f
        INNER JOIN HotelImages hi
        ON f.HotelID = id.HotelID
        INNER JOIN 
        (SELECT 

            id.HotelID,
            min(HotelImageID ) nextID
        from HotelImages hi
              inner join @foo f
              on f.HotelID = id.HotelID
        where id.HotelImageID  > f.lastid


        group by id.HotelID) Xnext

        ON Xnext.nextID = id.HotelImageID 



END 

SELECT 
    h.HotelID 
    h.HotelName,
    f.FileNameList
FROM
    Hotels h
    INNER JOIN @foo f
    ON h.hotelId = f.HotelId

---

Related Topics