Aggregate data from named column of multiple rows/records

Question

I am updating a site that is essentially a competition where by each contestant is voted on by (n) number of judges, with each judge leaving feedback which is stored in the column 'feedback', in the table 'recEntrantStatus'.

I need to be able to collect all feedback left for a given entrant, and collate this data before storing it in a variable in a stored procedure.

So for example to get all the feedback for a single entrant(eg all judge of 1 artist) I would use the following:

SELECT rndFeedback FROM recEntrantStatus WHERE roundId = 3 AND entrantId = @entrantId

However, I don't know how to work with this to collect ALL feedback from all judges for the current artist in the current round, and neatly collect this into 1 single declared variable that can then be used later in an insert.

A scenario to further clarify;

Its round 2 and 10 judges vote and leave feedback on a single entrant. I need to collect the feedback placed in each of the 10 records relating to each judge that votes on a specified entrant. This then needs to be aggregated into one declared variable in the existing Stored Process.

Each record would look something like this:

id | judgeId | entrantId | roundId | rndFeedback 
________________________________________________


1 | 5        | 22        | 2       | Awesome

1 | 8        | 22        | 2       | Really Nice Work

1 | 9        | 22        | 2       | The bass was a little heavy

1 | 10       | 22        | 2       | You Suck

1 | 11       | 22        | 2       | It was really good but lacking emotion

1 | 14       | 22        | 2       | You get my vote

1 | 15       | 22        | 2       | Nice Melody

So ultimately I would be looking to have collected all the feedback for entrantId = 22 as a single string of text which contains:

Awesome Really Nice Work The bass was a little heavy You Suck It was really good but lacking emotion You get my vote Nice Melody

P.s. rndFeedback is VARCHAR data type

Answer 1

DECLARE @t TABLE (
    id INT,
    judgeId INT,
    entrantId INT,
    roundId INT,
    rndFeedback VARCHAR(100)
)

INSERT INTO @t
VALUES 
    (1, 5 , 22, 2, 'Awesome'),
    (1, 8 , 22, 2, 'Really Nice Work'),
    (1, 9 , 22, 2, 'The bass was a little heavy'),
    (1, 10, 22, 2, 'You Suck'),
    (1, 11, 22, 2, 'It was really good but lacking emotion'),
    (1, 14, 22, 2, 'You get my vote'),
    (1, 15, 22, 2, 'Nice Melody'),
    (1, 15, 23, 2, 'TEST'),
    (1, 15, 23, 2, NULL),
    (1, 15, 24, 2, NULL)

SELECT t1.entrantId, STUFF((
    SELECT ' ' + rndFeedback
    FROM @t t2
    WHERE t2.entrantId = t1.entrantId
        AND t2.roundId = 2
        AND t2.rndFeedback IS NOT NULL
    FOR XML PATH('')), 1, 1, '')
FROM (
    SELECT DISTINCT entrantId
    FROM @t
    WHERE roundId = 2
        AND rndFeedback IS NOT NULL
) t1

output -

----------- ----------------------------------------------------------------------------------------------------------------------------------
22          Awesome Really Nice Work The bass was a little heavy You Suck It was really good but lacking emotion You get my vote Nice Melody
23          TEST

post - http://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server