Concatenating Column Values into a Comma-Separated string

Question

I have a table which looks like the following:

EventProfileID    ParamName       ParamValue
1                 _CommandText    usp_storedproc_1
2                 _CommandText    usp_storedproc_2
2                 _CommandText    usp_storedproc_3
2                 _CommandText    usp_storedproc_100
3                 _CommandText    usp_storedproc_11
3                 _CommandText    usp_storedproc_123

What I would like my output to be is the following:

EventProfileID    ParamValue
1                 usp_storedproc_1
2                 usp_storedproc_2, usp_storedproc_3, usp_storedproc_100
3                 usp_storedproc_11, usp_storedproc_123

However I am having some bother. If I do a select on one of the event profile ID's I can get an output using the following logic:

SELECT LEFT(c.ParamValue, LEN(c.ParamValue) - 1)
FROM (
SELECT a.ParamValue + ', '
FROM DP_EventProfileParams AS a
WHERE a.ParamName = '_CommandText'
and a.EventProfileId = '13311'
FOR XML PATH ('')
) c (paramvalue)

However that just gives me the output for one EventProfileID and also I would like the EventProfileID as part of the output.

Can anyone give me any pointers in the right direction into how I can expand my code to include this and allow the code to be dynamic so that I can show all EventProfileID's?

Thanks

score 2 · Accepted Answer · answered Dec 08 '15 at 16:42

You can do it this way:

select distinct a.EventProfileID,
stuff((select ','+ ParamValue)
       from DP_EventProfileParams s 
       where s.EventProfileID = a.EventProfileID 
       for XML path('')),1,1,'')
from DP_EventProfileParams a

You were on the right track with for XML path. STUFF function makes it easier to achieve what you want.

Lukasz Szozda · Answer 2 · 2015-12-08T16:47:14.913

The original query does not work because it uses simple subquery (works only for one specific id)

To make it work for all ids you can use XML + STUFF inside correlated subquery:

Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

SELECT DISTINCT
         EventProfileID,
         [ParamVaues] = 
         STUFF((SELECT ',' + d2.ParamValue
               FROM #DP_EventProfileParams d2
               WHERE d1.EventProfileID = d2.EventProfileID
                 AND d2.ParamName = '_CommandText'
               FOR XML PATH('')), 1, 1, '')
FROM #DP_EventProfileParams d1
ORDER BY EventProfileID;

LiveDemo

I strongly suggest reading Concatenating Row Values in Transact-SQL

Just like the duplicate. – Tab Alleman Dec 08 '15 at 16:44 — Tab Alleman, Dec 08 '15 at 16:44

Concatenating Column Values into a Comma-Separated string

2 Answers2