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

Here is my sample table:

I need output like this:

But I am getting output this way :

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.

CodePudding user response：

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.