sql server - sql concat within a SELECT statement -

This is similar to this one

but not enough MS SQL 2008 is it. I am pulling patient demographics, and one of them is a race, which is a multi-option field (for example you can be Asian and Chinese). The race table 'Patient fibers' is added by the patient from the patient table. Therefore the table structure is as follows:

  Patient table Patient Patient name Patient Leaf Patellary Table Table PatientRaceID PatientID (FK) Description  

Only one line Should I want to include the race, is it possible to do it within a single selection statement or do I need to have a cursor? I am making the cursor in this way: Select all initial demographics, go through a temporary table by the patient id in a temporary table, then for each, grab the race, contact, and add to the temporary table .

The desired output is as follows: 1 row per patient.

Name: "Jo Blow"

Race: "Asian, Chinese"

Inserting string values ​​in SQL Server is not clear; for this it is necessary to use" XML "data processing and a subquery:

  select p. *, (Select from the condition of the patient (select the ',' + Description, where PMITID = ('')), 1, 2, '') as a race from patient P;