group by without aggregate functions and row grouping

Question

Hi guys I have a table that has 2 columns

LoadID      coment
1234        comment1
1234        comment2
1234        comment3
12345       comment4
28366356    comment5
28366356    comment6
1212        comment7

I am trying to run this code to get the comments for a specific LoadID to group together which should look like this (comment1,comment2,comment3)

SELECT coment + ',' AS 'data()'
FROM TB_SOT_Comment
Where LoadID = '1234'
FOR XML PATH('')

The code is working fine but the problem is that I want to change the where clause to do the same for all the loadID's. The catch is that the loadID's are not constants and they change all the time based on the operator input so I can't really specify the number I just need to find a way to write a code that groups all the similar loadid's in the table and connect the comments of that loadID together so basically I want the output to look like this

LoadID      coment
1234        comment1,comment2,comment3
12345       comment4
28366356    comment5,comment6
1212        comment7

Answer 1

Use Distinct Keyword For Remove duplicacy

select distinct LoadId,(SELECT coment + ',' AS 'data()' FROM TB_SOT_Comment Where LoadID = t.LoadID FOR XML PATH('')) from TB_SOT_Comment t

Answer 2

In SQL Server -- prior to the most recent version -- you would use a correlated subquery:

SELECT l.LoadId,
       STUFF( (SELECT ',' + comment -- AS 'data()'
               FROM TB_SOT_Comment c2
               WHERE c2.LoadID = l.LoadId
               FOR XML PATH ('')
              ), 1, 1, ''
            ) as comments
FROM (SELECT DISTINCT LoadId FROM TB_SOT_Comment) l;

Notes:

• The correlation clause (the inner WHERE) is key to this query.
• I added STUFF() so the comments don't end in a comma.
• The distinct loads come from a subquery. This is more efficient, because the subquery is run only once per load.
• I commented out the AS 'data()'. To be honest, I simply haven't used that construct.