List Multiple Row Values on Single Row (Comma Separated List)

Hey every and any body! Welcome back (or welcome to) SQL Something!

The main post for this month is looking at how to create a comma separated list for a one to many relationship (found either within the same table or between two tables).

Let's look at the following. Suppose I have the following values in a table:

Fig. 1: Transactions and their Messages...

You can see there exists a one to many relationship between this table (Table1) and another table (Table2) where multiple messages in Table1 correspond to single transaction in Table 2.

Suppose we wanted to list only the transactions and their corresponding messages. We can list the results as follows:

Fig. 2: List of Transactions and their Messages...

But suppose we want to list all the corresponding messages in one field as follows:

Single row list of Transactions and their Messages...

To achieve that we'll have to use FOR XML, specifically the FOR XML PATH variant. We can then use the below query:

--------------------------------------------------------------------------------------

SELECT T1.Tran_ID,
        (   SELECT       T2.Message + ','
            FROM          [Table1] T2
            WHERE       T2.Tran_ID = T1.Tran_ID
            ORDER BY T2.Message
            FOR XML PATH('')
        ) AS Transactions
FROM [Table1] T1
GROUP BY Tran_ID;

--------------------------------------------------------------------------------------

What this query does is reference itself with a subquery for each instance of a Tran_ID. For each instance of a Tran_ID, it looks for the corresponding messages and, using FOR XML PATH(''), it concatenates them with a comma separator.

By providing a 'blank' hierarchy in the FOR XML PATH clause, SQL does not create any element tabs and so we get one continuous string. A very good read up on the various FOR XML variations can be found here.

And that's it. :-)

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)