CODEWITHSUNDEEP
sql-server
Raven
Raven

Reputation: 859

Concatenate Two Separate Columns of Text Per ID

I know it's possible to concatenate one row using the following code. For example making one row of StudentName text per subject ID.

Example Data

  Patient_FIN       StudentName
  ----------      -------------
  1               Mary
  1               John
  2               Alaina
  2               Edward

Expected Results

 Patient_FIN       StudentName
 ----------      -------------
 1               Mary, John
 2               Alaina, Edward

Code used to get the above output

SELECT DISTINCT ST2.pt_fin, 
        SUBSTRING(
                 (
            SELECT ST1.StudentName + ',' AS [text()]
            FROM ED_ORDERS_IMPORT_MASTER ST1
            WHERE ST1.Patient_FIN = ST2.Patient_FIN
            ORDER BY ST1.Patient_FIN
            FOR XML PATH ('')
        ) , 2, 1000) [Patient]
      FROM ED_ORDERS_IMPORT_MASTER ST2

However, let's say I have the same data and an additional row that I'd also like to concatenate into a separate column based on Patient_FIN:

   Patient_FIN       StudentName    Color
  ----------      -------------   ----------
    1               Mary           Blue  
    1               John           Red
    2               Alaina         Red
    2               Edward         White

Desired Results

 Patient_FIN       StudentName         Color
 ----------      -------------       ----------
 1               Mary, John           Blue, Red
 2               Alaina, Edward       Red, White

How can I edit the above code to produce the desired results? Thanks!

Upvotes: 0

Views: 58

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

You can use STUFF to get your desired output as below-

SELECT Patient_FIN,
STUFF
(
    (
        SELECT ',' + StudentName 
        FROM your_table 
        WHERE Patient_FIN = A.Patient_FIN
        FOR XML PATH ('')), 1, 1, ''
) StudentName,
STUFF
(
    (
        SELECT ',' + Color 
        FROM your_table 
        WHERE Patient_FIN = A.Patient_FIN
        FOR XML PATH ('')), 1, 1, ''
) Color
FROM your_table A
GROUP BY Patient_FIN

Output is- 

Patient_FIN StudentName     Color
1           Mary,John       Blue,Red
2           Alaina,Edward   Red,White

Upvotes: 1

Related Questions