Order by on specific id in subquery with STUFF()

Question

I am using SQL Server and facing problem to get my required data.

Here is my sample table:

Date	PlayID	Name	City
11/20/2022	101	Rishi	Mumbai
11/20/2022	102	Smita	New Mumbai
11/21/2022	102	Maiyand	Bangalore
11/22/2022	102	Rishi	Mumbai
11/22/2022	101	Smita	New Mumbai
11/23/2022	101	Maiyand	Bangalore
11/23/2022	102	Smita	New Mumbai

I need output like this:

Date	Name
11/20/2022	Rishi,Smita
11/21/2022	Maiyand
11/22/2022	Smita,Rishi
11/23/2022	Maiyand,Smita

But I am getting output this way :

Date	Name
11/20/2022	Rishi,Smita
11/21/2022	,Maiyand
11/22/2022	Rishi,Smita
11/23/2022	Maiyand,Smita

You can see there is a difference of names order in Name column. SQL Server is making STUFF() on how the records are inserted in main table. But I want records in similar manner, means if you see bold values in required table: Name is like Smita,Rishi even Rishi is inserted before Smita. But the actual output I am getting is like Rishi,Smita.

It will be ok if all the records will return like Rishi,Smita or Smita,Rishi and no problem with single names.

My SQL statement:

SELECT DISTINCT
    Date, 
    STUFF((SELECT ',' + Name (SELECT Name FROM PlayGroup _p 
                              WHERE _p.Date = P.Date) PL 
           FOR XML PATH('')), 1, 1, '') AS Name 
FROM
    (SELECT DISTINCT
         Date, PlayID, Name 
     FROM
         PlayGroup P 
     WHERE 
         1 = 1) Q
WHERE
    Q.Date 
ORDER BY
    Desc

I tried to put PlayID in order by but I don't want to select it. Because I want distinct records and arrange Names on the basis of asc PlayID.

Answer 1

If all you are asking is how to use an ORDER BY in the STUFF(SELECT FOR XML PATH()) function, then try this (I simplified the query a bit):

SELECT DISTINCT
    Date, 
    STUFF((
        SELECT ',' + Name
        FROM PlayGroup _p 
        WHERE _p.Date = P.Date
        ORDER BY _p.PlayID ASC --Added the ORDER BY here.
        FOR XML PATH('')
    ), 1, 1, '') AS Name 
FROM PlayGroup p
ORDER BY p.Date ASC

If you asking for something more than just how to use the ORDER BY, then please clarify your question as it is hard to follow just exactly what you're looking form.

Answer 2

Seems your query is a lot more complicated than it needs to be. And since you want to order when you are building the comma-separated list, that's where the order by goes.

SELECT DISTINCT P.Date, Name = STUFF(
  (SELECT ',' + Name 
     FROM dbo.PlayGroup AS _p 
     WHERE _p.Date = P.Date
     ORDER BY PlayID -- <-- here is the ORDER BY
     FOR XML PATH('')
  ), 1, 1, '')
FROM dbo.PlayGroup AS P 
ORDER BY P.Date;

• Example db<>fiddle

As an aside, STUFF() isn't doing anything here at all, except for removing a leading comma.