Using XML PATH in Sql Stored Procedure

I'm trying to get a row with the concatenation of a welders names. This is what i've got:

SELECT  jsd1.JuntaSoldaduraID, 
        REPLACE(RTRIM((SELECT s1.Nombre + ' ' + s1.ApPaterno + ' ' + 
            s1.ApMaterno + '' + CAST('' AS VARCHAR(MAX)) + ' ' 
FROM JuntaSoldaduraDetalle jsd
INNER JOIN  Soldador s1 on s1.SoldadorID = jsd.SoldadorID
WHERE (jsd1.JuntaSoldaduraID = jsd.JuntaSoldaduraID) 
          and (jsd.TecnicaSoldadorID = 2)
FOR XML PATH (''))),' ',', ') AS NombreSoldador
FROM JuntaSoldaduraDetalle jsd1
INNER JOIN Soldador s
ON s.SoldadorID = jsd1.SoldadorID
GROUP BY jsd1.JuntaSoldaduraID

I'm able to get the information that i want but with a little problem. What I want is "John Smith, David Rogers, Peter Simons" etc.. in other words, full names separated by commas. But i'm receiving "John, smith, David, Rogers, Peter, Simons"..

Any help appreciated.

Thanks in advance.

Answers


Your REPLACE will replace all spaces with a comma. Instead, make the comma part of your query and use STUFF to remove the first occurrence of the comma. You might also want to incorporate the use of COALESCE in case any of the name fields are NULL.

SELECT  jsd1.JuntaSoldaduraID, 
        STUFF((SELECT ', ' + COALESCE(s1.Nombre + ' ','') 
                           + COALESCE(s1.ApPaterno + ' ','') 
                           + COALESCE(s1.ApMaterno,'')
                   FROM JuntaSoldaduraDetalle jsd
                       INNER JOIN  Soldador s1 
                           on s1.SoldadorID = jsd.SoldadorID
                   WHERE jsd1.JuntaSoldaduraID = jsd.JuntaSoldaduraID 
                       and jsd.TecnicaSoldadorID = 2
                   FOR XML PATH ('')),1,2,'') AS NombreSoldador
    FROM JuntaSoldaduraDetalle jsd1
        INNER JOIN Soldador s
            ON s.SoldadorID = jsd1.SoldadorID
    GROUP BY jsd1.JuntaSoldaduraID

Need Your Help

Memory Allocation Error in C

c memory-management malloc realloc gmp

I am trying to get the binary representation of a big integer in GMP. I am storing 1's and 0's in an array called expBinary. I use malloc to allocate a memory of size of "int", then use realloc to

Android Facebook Unsupported post request error

java android facebook sdk

I am working on a Facebook application and am currently trying to have my app tag one of the user's friends. I almost have it working 100%, except when it is supposed to be tagging the person, I i...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.