I have a table which contains data about a number of bands. Each band has numerous records in this table, which contain various bits of info and importantly feedback for them from some judges.
The table structure is essentially:
id | judgeId | entrantId | roundId | rndFeedback
The following SQL grabs all the feedback for each band and collates it into a single record. eg:
DECLARE @t TABLE (
id INT,
judgeId INT,
entrantId INT,
roundId INT,
rndFeedback VARCHAR(100)
)
INSERT INTO @t
VALUES
(1, 5 , 22, 2, 'Awesome'),
(1, 4 , 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, 4, 23, 2, 'TEST'),
(1, 15, 23, 2, NULL),
(1, 4, 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
But, I now want to be able to take this feedback and update all the records for that band so that every record would contain the same collated feedback for that artist.
So for example the table would now look like:
(1, 5 , 22, 2, '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'),
(1, 4 , 22, 2, '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'),
(1, 9 , 22, 2, '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'),
(1, 10, 22, 2, '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'),
(1, 11, 22, 2, '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'),
(1, 14, 22, 2, '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'),
(1, 15, 22, 2, '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'),
(1, 4, 23, 2, 'TEST'),
(1, 15, 23, 2, 'TEST'),
(1, 4, 24, 2, NULL)
I've no idea how to go about doing this, as this is getting beyond my current level of t-sql knowledge.
Thanks!