MSSQL: Concatenate many rows into a single string

[cad4321]

Hi,

I have a table as below. It is just a sample.


Sales Amt Sales Person
------------ ---------------
142 Mary
142 John
142 Sam
222 Alaina
222 Edward

I want to get it as

Sales Amt Sales Person
------------ ---------------
142 Mary, John, Sam
222 Alaine, Edward

[RonR]

Sweet .... I remember i had a similar problem which i solved through interface language instead of SQL. Very tiresome ..... Anyhow time changed .... Here is how you can do it. You lucky Man!

Select distinct ST2.SAmt, 
               substring((Select ','+ST1.SName AS [text()]
                From dbo.Salers ST1
                Where ST1.SAmt = ST2.SAmt
                ORDER BY ST1.SAMT
                For XML PATH ('')),2, 1000) [Sales]
         From dbo.Sales ST2

[cad4321]

Thank you very much.