Is there an alternative for XML PATH in SQL

Question

I am developing a clinic management application.
In that application there is a part where doctors choose what they use up in each service they provide.
For example, when a doctor examines a patient, a pair of gloves and wooden stick are used and disposed of.

I use a trick that is simply, I create a string in the service table, that string has the IDs separated with comma (something like that 1,3,7,) and it works fine except for one case.
That case is when I want to display the used up item names as one column for multiple services.
I get items of service one plus service two and so on as one string for all service.
Is there any way I can solve that? I know it sounds complicated but If it works it will many other you read this.

The way I use to join is:

on CHARINDEX(CAST(TblSupply.IDSupply as varchar(10)), TblService.UsedSupplyIDs) > 0  

Simply I cast the Used Item ID TblSupply.IDSupply as string then check if it exists in the service table.

When I display as separate columns, it works fine but I get multiple rows for the same service as follows:

Select
  TblService.ServiceName,
  TblSupply.SupplyName
from
  TblService
  left join TblSupply on CHARINDEX(CAST(TblSupply.IDSupply as varchar(10)), TblService.UsedSupplyIDs) > 0   

e.g.

 _____________________________________________
|TblService.ServiceName|TblSupply.SupplyName |
|Patient examination   |Glove                |
|Patient examination   |wood stick           |
|Patient examination   |thermometer          |
|Sonar   examination   |Glove                |
|Sonar   examination   |lubricating Gel      |
|Consultation          |Glove                |
|______________________|_____________________|

I want to get

________________________________________________________
|TblService.ServiceName|xxxxx                           |
|Patient examination   |Glove ,wood stick , thermometer |
|Sonar   examination   |Glove,lubricating Gel           |
|Consultation          |Glove                           |

The code I made

Select
   TblService.ServiceName,TempTable.SupplyList
from
   TblService
   left join (Select TblService.IDService As IDService,
                     STUFF((Select ', ' + TblSupply.SupplyName
                            FROM
                              TblService
                              left join TblSupply on CHARINDEX(CAST(TblSupply.IDSupply as varchar(10)), TblService.UsedSupplyIDs) > 0
                              FOR XML PATH('')), 1, 1, '') as SupplyList
              FROM
                  TblService
              GROUP BY
                  TblService.IDService
             ) as TempTable on TempTable.IDService=TblService.IDService  

I tried

Select
    TblPatientService.IDPatientService,
    TblService.ServiceName,
    TempTable.SupplyList
from
    TblPatientService
    left Join TblService On TblService.IDService = TblPatientService.IDService 
    left join (Select TblPatientService.IDPatientService As IDPatientService
                      STUFF((Select ', ' + TblSupply.SupplyName
                             FROM
                                TblPatientService
                                left join TblService on TblService.IDService = TblPatientService.IDService
                                left join TblSupply on CHARINDEX(CAST(TblSupply.IDSupply as varchar(10)), TblService.UsedSupplyIDs) > 0
                             WHERE
                                TblPatientService.IDService = TblService.IDService
                             FOR XML PATH('')), 1, 1, '') as SupplyList
               FROM
                   TblPatientService
                   left Join TblService On TblService.IDService = TblPatientService.IDService
               GROUP BY
                   TblPatientService.IDPatientService
              ) as TempTable on TempTable.IDPatientService = TblPatientService.IDPatientService  

What I really get

|TblService.ServiceName|xxxxx                                                                         
|Patient examination   |Glove ,wood stick , thermometer,Glove,lubricating Gel,Glove,lubricating Gel   |
|Sonar   examination   |Glove ,wood stick , thermometer,Glove,lubricating Gel,Glove,lubricating Gel   |
|Consultation          |Glove ,wood stick , thermometer,Glove,lubricating Gel,Glove,lubricating Gel   |

In other words I get all the used items for all services as if they used for one service for all displayed.

Answer 1

Since SQL Server 2017, you can use STRING_AGG. Somtehing like :

SELECT TblPatientService.IDPatientService AS IDPatientService, 
       STRING_AGG(TblSupply.SupplyName, ', ') AS SupplyList
FROM   TblPatientService
       LEFT OUTER JOIN TblService ON TblService.IDService = TblPatientService.IDService
       LEFT OUTER JOIN TblSupply ON CHARINDEX(CAST(TblSupply.IDSupply AS VARCHAR(10)), TblService.UsedSupplyIDs) > 0
GROUP BY TblPatientService.IDPatientService;

To replace XML stuff !

Answer 2

This is too long for a comment.

I would strongly suggest fixing your data model. Storing delimited lists in database columns is a typical design antipattern, that will backfire in different ways (complexity, efficiency, maintenance, data integrity). You can ha have a look at this famous SO question for more details.

As an exemple: your current join condition will not do what you expect for some supply ids that are more than one digit long (typically, 1 would match 10).

You should normalize your model and have a separate table to store the N-M relationship between services and supplies, with one tuple per row.