SQL queries for combing multiple rows into one row

[Lei]

In MS-SQL, it didn't provide sql functions like  SELECT CONCAT queries. how to implement similar functions in ms-sql? for example:

itemA   n1   x1

itemB   n2   h2

itemA   n1   x2

itemA   n1   x3

we need to combine all rows of itemA into this:

itemA n1   x1,x2,x3

itemB n2   h2

In my-sql, it's easy to get this by using select concat ...how to achieve this in ms-sql?

[Lei]

by using the follwoing way, we can achieve the similar result like select concate....

select distinct p1.ClinicNumber,

(select MedProcDesc + ',' 

from  dbo.OR_Procedures_2012 as t1

where 

t1.EnvironmentStarted between DateAdd(Day, DateDiff(Day, 9, GetDate()), 0) and DateAdd(Day, DateDiff(Day, 2, GetDate()), 0)  AND  t1.MedProcTypeDesc  =  'Cardiac' and t1.clinicNumber=p1.clinicNumber 

order by MedProcDesc FOR XML PATH('') ) as procedures

from dbo.OR_Procedures_2012 as p1,

(

SELECT distinct ClinicNumber FROM dbo.OR_Events_2012 WHERE EventDocumentedDatetime  between DateAdd(Day, DateDiff(Day, 9, GetDate()), 0) and DateAdd(Day, DateDiff(Day, 2, GetDate()), 0)  AND EventDesc like '%bypass%') as p2

where p1.ClinicNumber= p2.ClinicNumber and 

p1.EnvironmentStarted between DateAdd(Day, DateDiff(Day, 9, GetDate()), 0) and DateAdd(Day, DateDiff(Day, 2, GetDate()), 0)  AND  (p1.MedProcTypeDesc  =  'Cardiac')

the key part is to join to itself and use "for xml path("")" method to combine multiple rows into one row in order to get a result.