Concatenate Two Separate Columns of Text Per ID

Question

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!

It is the same exact concept. You know how to get the data for StudentName, rinse and repeat to get the data for Color. — Sean Lange, Jul 22 '19 at 14:02
I would suggest replacing `SUBSTRING` with `STUFF` too, and **replacing** the first character with `''` instead. — Thom A, Jul 22 '19 at 14:12
So I added an additional line of code under the first select command "ST1.Color + ',' AS [text()]". This concatenates the color column but adds it to the name column (I want the color column to be independent of the name column) — Raven, Jul 22 '19 at 14:21

score 1 · Accepted Answer · answered Jul 22 '19 at 14:29

1

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

answered Jul 22 '19 at 14:29

mkRabbani

16,295
2
15
24

Sorry I made a mistake there is no error-thanks! Brilliant code! – Raven Jul 22 '19 at 15:27
Out of curisoity what does the command do for the code? FOR XML PATH ('')), 1, 1, '' – Raven Jul 22 '19 at 15:28
1

That's part of STUFF method. – mkRabbani Jul 22 '19 at 15:48
so what is the "('')), 1, 1, " telling code to do? – Raven Jul 23 '19 at 16:51
1

You will get some details here - https://stackoverflow.com/questions/21623593/what-is-the-meaning-of-select-for-xml-path-1-1 – mkRabbani Jul 23 '19 at 17:00

Concatenate Two Separate Columns of Text Per ID

1 Answers1