|
I have a temporary table with a field called Method, thus:
Method will be filled from several rows in another table - CaseEventTypeList. Running
Gives
Turning this into a single comma delimited result is fairly trivial:
Giving:
Now I need to expand this to take in the entire table. This is what I came up with:
However this only fills Method with the first value from each set of values. I looked online and found this but would rather not use a udf - especially when the solution feels so close. UPDATE: The data is fairly simple, the RefId is incremented for each case, the TypeID can be any number, though only 1 to 8 are modelled currently. Thus you might have:
And this will hopefully be modelled as
(comments are locked)
|
use this to update the @CaseSites table Heh... I'm confused, Kev... you post a 2k only question and then post a 2k5 answer. Which is it? A 2k or 2k5 problem?
Mar 07 '10 at 01:09 AM
Jeff Moden
Jeff, this isn't my question! The tags got messed around with a bit, and I agree this is a 2k5 only answer
Mar 12 '10 at 06:52 PM
Kev Riley ♦♦
Cool. This has just saved me a headache.
Oct 01 '10 at 07:23 AM
ThomasRushton ♦
(comments are locked)
|
|
You can use a CTE or subquery to correlate and concatenate them the way you want and then do the update. There are numerous methods of doing the concatenation. Some of them are discussed in Jeff Moden's great article: http://www.sqlservercentral.com/articles/Test+Data/61572/ And there is discussion of wrapping that in a CTE at: http://www.sqlservercentral.com/articles/Common+Table+Expression+(CTE)/62404/
(comments are locked)
|
|
workable? Think it does what you want, just replace table names as necessary. Kev's version is cleaner :) I think that was my FIRST edit but I couldnt get STUFF to lose the last comma but I can see where ', ' (space inserted) works now. Yeah, OP had this tagged specifically as SQL2000... can't change the conditions to fit your answer Tim.
Nov 05 '09 at 03:42 PM
Christopher Klein
I didn't think Tim had retagged just so his answer fits!! :)
Nov 05 '09 at 04:15 PM
Kev Riley ♦♦
Heh. Completely slipped my mind that this was for 2000 by the time I added the CTE stuff.
Nov 06 '09 at 02:10 AM
TimothyAWiseman
(comments are locked)
|


TimothyAWiseman : why remove the SQL2000 tag?
I've added the tag back. No reason to remove it.
Because I goofed. I meant to add the other tags, not remove the original.