String Concat with Join Sqlserver

Question

Good afternoon,

I’m having a problem with creating a command line in SQL SERVER 2014 where it joins information from two tables in one row.

I tried to use it that way

SELECT  DISTINCT  SUBSTRING ( ISNULL ( STUFF (
                                        ( SELECT convert(varchar(10), C.nomeRazaoSocial) + ','  AS codigo
                                          FROM PEDIDOSERVICO PS
                                          LEFT JOIN CLIENTE C ON (C.ID = PS.MECANICO_ID)                                  
                                      FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 0, '' ),
                                      '' ), 1, 254 ) 
                                         FROM PEDIDOSERVICO PS
                                            LEFT JOIN CLIENTE CL ON CL.ID = PS.MECANICO_ID   

but he is returning so:

And I wanted him to return that way:

TECHNICAL, TECNICO2

I tried a lot of different ways and they all didn’t work out.

One of them was:

    SELECT DISTINCT CONCAT((SELECT ',' + CLIENTE.nomeRazaoSocial AS [text()]
                             FROM CLIENTE T
                              FOR XML PATH ('')), 2, 1)
  FROM PEDIDOSERVICO 
  JOIN CLIENTE ON CLIENTE.ID = PEDIDOSERVICO.MECANICO_ID     
      WHERE PEDIDO = 54301

Another way I tried was like this:

SELECT DISTINCT SUBSTRING((SELECT ',' + CLIENTE.nomeRazaoSocial AS [text()]
                             FROM CLIENTE T
                              FOR XML PATH ('')), 2, 8000)
  FROM PEDIDOSERVICO 
  JOIN CLIENTE ON CLIENTE.ID = PEDIDOSERVICO.MECANICO_ID     
      WHERE PEDIDO = 54301

Who also returns like this:

Thank you in advance!

Answer 1

To get the desired result, just insert a second DISTINCT in consultation as follows:

SELECT  DISTINCT  SUBSTRING ( ISNULL ( STUFF (
                                        ( SELECT DISTINCT convert(varchar(10), C.nomeRazaoSocial) + ','  AS codigo
                                          FROM PEDIDOSERVICO PS
                                          LEFT JOIN CLIENTE C ON (C.ID = PS.MECANICO_ID)                                  
                                      FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 0, '' ),
                                      '' ), 1, 254 ) 
                                         FROM PEDIDOSERVICO PS
                                            LEFT JOIN CLIENTE CL ON CL.ID = PS.MECANICO_ID