SQL How to add values of a select statement to a varchar

Question

I'm writing a function that returns the names of a child table's one column's values in one varchar.

The relation is: I have a parent table called Activity. And a child table in N-1 relation with table Activity, called ActivityObjective. And a third table where I keep the names of the objectives, called Objective.

This is the query that I make. This returns the names of the objectives of a specific Activity with ActivityID = @ActivityID

SELECT      o.ObjectiveName      
FROM        Activity a    
INNER JOIN  ActivityObjective ao ON a.ActivityID = ao.ActivityID      
INNER JOIN  Objective o ON o.ObjectiveID = ao.ObjectiveID     
WHERE       a.ActivityID = @ActivityID

This returns something like:

    ObjectiveName
|-------------------|
    objName1
    objName2
    objName3

My aim is no have a varchar "objName1, objName2, objName3". I cannot create a temp table because I'm working in a function.

see this example http://stackoverflow.com/questions/5196371/sql-query-concatenating-results-into-one-string — StackTrace, Jan 06 '14 at 12:45
Google for [sqlserver group_concat](https://duckduckgo.com/?q=sqlserver+group_concat). Be the 1.000.000th developer to wonder why Microsoft makes this so hard. — Andomar, Jan 06 '14 at 12:45

score 0 · Answer 1 · answered Jan 06 '14 at 12:47

You will need to adjust the following to match what you specifically wanted, but this is a start:

Select  substring((
    SELECT (', ' +  o.ObjectiveName)
    FROM        Activity a    
    INNER JOIN  ActivityObjective ao ON a.ActivityID = ao.ActivityID      
    INNER JOIN  Objective o ON o.ObjectiveID = ao.ObjectiveID     
    WHERE       a.ActivityID = @ActivityID
    FOR XML PATH( '' )
), 3, 1000 )

SQL How to add values of a select statement to a varchar

1 Answers1