CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Phill Healey
Phill Healey

Reputation: 3180

Aggregate data from named column of multiple rows/records

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

Upvotes: 0

Views: 60

Answers (2)

David Rushton
David Rushton

Reputation: 5030

Yes you can do this. I've include an example below, using this sample data:

Sample Data

/* Using a table variable allows everyone to 
 * share the same data.
 */
DECLARE @Sample TABLE
    (
        Id                INT,
        JudgeId            INT,
        EntrantId        INT,
        RoundId            INT,
        rndFeedBack        VARCHAR(50)
    )
;

/* Populated based on values supplied in OP.
 */
INSERT INTO @Sample
    (
        Id,
        JudgeId,
        EntrantId,
        RoundId,
        rndFeedBack
    )
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, 22, 2, NULL)
;

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:

  • NULLs are replaced with a blank string.
  • rndFeedBack is prefixed with a comma if @Feeback already contains text.

Example

/* This will hold the concatenated value.
 * You must initialise this variable, to avoid NULL + text = NULL.
 */
DECLARE @Feedback VARCHAR(MAX) = '';

SELECT
    @Feedback = @Feedback +
        CASE
            WHEN rndFeedBack IS NULL    THEN ''
            WHEN LEN(@Feedback) > 1     THEN ',' + rndFeedBack
            ELSE rndFeedBack
        END
FROM
    @Sample
WHERE
    EntrantId = 22
    AND RoundId = 2
;


/* Check the returned value.
 */
SELECT
    @Feedback
;

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.

Upvotes: 1

Devart
Devart

Reputation: 121982

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

Upvotes: 1

Related Questions