How to Coalesce MSSQL query result on specific column

Question

I'm running a simple join query as follow

 Select T.tno,P.GMass,t.TicketNo,t.SchedID,t.Compound from TicketBatch 
 T  join PalletWeight P on T.tno=P.Tno where isProcesed = 0 and   P.TNo
 =(select MAX(tno) from PalletWeight)

Result query is

Id like to have a more summarized result that would give one one field of the tno , gmass ,schedID and compound (this will always be identical) .The TicketNo would have to be coalesced .

ie

tno , Gmass , TicketNo , SchedID , Compound

(250) , (200.00) , (105132,105133,105134), 41729 ,Null

Any help would be appreciated

No, this isn't what `coalesce` does. You are looking to CSV aggregate the ticket number for each group. — Tim Biegeleisen, May 30 '17 at 09:22
Possible duplicate of [Optimal way to concatenate/aggregate strings](https://stackoverflow.com/questions/13639262/optimal-way-to-concatenate-aggregate-strings) — Andrey Korneyev, May 30 '17 at 09:23

score 0 · Answer 1 · answered Oct 30 '17 at 19:44

You can use FOR XML PATH to accomplish this - might not be the most elegant way, but it should work:

SELECT
    tno
,   GMass
,   '(' + LEFT(tickets, LEN(tickets)-1) + ')' AS TicketNo
,   SchedId
,   Compound
FROM (
    SELECT DISTINCT
        T.tno
    ,   P.GMass
    ,   (   SELECT TicketNo + ','
            FROM TicketBatch T2
            WHERE T.tno = T2.tno
            FOR XML PATH('') ) AS tickets
    ,   T.SchedId
    ,   T.Compound
    FROM TicketBatch T
    JOIN PalletWeight P
        ON T.tno = P.tno
) X

You basically create a AAA,BBB,CCC, string with the inner-most query using XML PATH and select it as a field, then use the outer query to add the parentheses and trim the trailing comma off. You get the correct ticket list by using the WHERE T.tno = T2.tno. I assumed tno was the primary key based off your query and used that to pull the correct ticket list.

How to Coalesce MSSQL query result on specific column

1 Answers1