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
2条答案
按热度按时间4urapxun1#
output -
post - http://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server
zqry0prt2#
Yes you can do this. I've include an example below, using this sample data:
Sample Data
First you will need to declare a variable to hold the output. Make sure it's big enough to hold the entire result or you will be presented with a trimmed value (trimming will not raise an error so it's easy to miss).
This technique requires you to initialize the variable, because the default value for a VARCHAR variable is NULL. And NULL + 'any text' = NULL. Our select statement below adds each returned value to the variable.
Finally you can use a CASE Expression to conditionally build the return value. In this example:
Example
EDIT 1: Added ISNULL to example code.
EDIT 2: Added CASE expression to the example code. Added explanation of case to preceding paragraph. Added NULL record to sample data.