Comma placement in string 2

[Whippingboy2004]

I'm using a cursor to concatenate the values of a one-to-many relationship into a single column. See I read the FAQ's, specifically faq 183-2146! What I'm having an issue with is the comma separating each value. Depending on my placement of the comma in the statement, I get either an extra trailing or preceding comma that's superfluous.

Ex. , x, y, z
OR
x, y, z,




WHILE @@Fetch_Status=0
BEGIN
IF @CurrKey <> @PrevKey
IF @PrevKey !=-1
BEGIN
INSERT #Concat
VALUES(@PrevKey, @BigLine)
SET @BigLine=''
END
SET @PrevKey=@CurrKey
SET @BigLine=(@BigLine+'')+(@CurrLine+', ') --These are the two possibilities I've tried.
--SET @BigLine=(@BigLine+', ')+(@CurrLine)

SET
FETCH myCursor INTO @CurrKey, @CurrLine
END

This is probably something fairly simple to accomplish, but I just can't see it right now.
Thank you in advance for you help,
T.

 

[donutman]

Just delete it by taking a substring (@BigLine, 3, 1000) when it is at the beginning. BTW, I think you can do what you want without a cursor (which would be much more efficient). Let's see the table.
-Karl

 

[Whippingboy2004]

Karl,

Thank you so much for your help. I've been trying to find a work around for the cursor, but haven't had much success so far.

Here's the data from my table:

OutreachID Descript CaseID RecipientID
----------- ------------------------------ ----------- -----------
3 BBTD 545 131
3 BBTD 545 135
3 BBTD 545 148
3 BBTD 545 149
3 BBTD 545 131
3 BBTD 545 135
3 BBTD 545 148
3 BBTD 545 149
3 CSHCN 545 131
3 CSHCN 545 135
3 CSHCN 545 148
3 CSHCN 545 149
3 CSHCN 545 131
3 CSHCN 545 135
3 CSHCN 545 148
3 CSHCN 545 149
3 Other TP 545 131
3 Other TP 545 135
3 Other TP 545 148
3 Other TP 545 149
3 Other TP 545 131
3 Other TP 545 135
3 Other TP 545 148
3 Other TP 545 149
4 Adolescent 546 264
4 Adolescent 546 264
4 BBTD 546 264
4 BBTD 546 264
4 Dental 546 264
4 Dental 546 264
5 Dental 547 183
5 Dental 547 184
5 Dental 547 183
5 Dental 547 184
5 Dental 547 183
5 Dental 547 184
5 DPRS 547 183
5 DPRS 547 184
5 DPRS 547 183
5 DPRS 547 184
5 DPRS 547 183
5 DPRS 547 184
5 Missed Appointment 547 183
5 Missed Appointment 547 184
5 Missed Appointment 547 183
5 Missed Appointment 547 184
5 Missed Appointment 547 183
5 Missed Appointment 547 184
5 School 547 183
5 School 547 184
5 School 547 183
5 School 547 184
5 School 547 183
5 School 547 184
6 Adolescent 548 123
6 Adolescent 548 158
6 Adolescent 548 189
6 DPRS 548 123
6 DPRS 548 158
6 DPRS 548 189
6 Foster Care 548 123
6 Foster Care 548 158
6 Foster Care 548 189
7 Foster Care 542 150
7 Foster Care 542 152
7 Head Start 542 150
7 Head Start 542 152
8 Head Start 550 227
8 Head Start 550 233
8 HCO 550 227
8 HCO 550 233
9 HCO 590 145
9 HCO 590 146
9 HCO 590 145
9 HCO 590 146
9 HCP Referral 590 145
9 HCP Referral 590 146
9 HCP Referral 590 145
9 HCP Referral 590 146
10 Health Fair 567 218
10 Migrants 567 218


Each OutreachID can have multiple Descript(s) and each CaseID can have multiple RecipientIDs. What I'm trying to concatenate is the Descript column, so for OutreachID 3 I would have a Descript of BBTD, CSHCN, Other TP.

I hope this is descriptive enough for you. Do you need my code too? If so, do you want part or all? I want to get you the info you need, but I don't want to indulge in diarrhea of the keyboard, so to speak.

Thank you,
T.

 

[donutman]

You will need to create a function

Create Function dbo.ConcatDesc (@OutreachID int) 
   returns varchar(1000)
AS
BEGIN
   Declare @Str as varchar(1000)
   Set @Str=''
   Select @Str=@Str+', '+Descript from MyTable
      where OutreachID=@OutreachID
   return Substring(@Str, 3, 1000)
END

Then use it like this:

Select OutreachID, dbo.ConcantDesc(OutreachID) from MyTable
   group by OutreachID

In a way this is like using a cursor because the function has to be called for each new OutreachID but then it requires a subquery to be called. So for some datasets it might be more efficient to use a cursor! If this runs very slow and you have to run it frequently, you may want to try the cursor approach.
-Karl

 

[Whippingboy2004]

Karl,
Thank you so much. I'm creating the function now and I'll let you know how it turns out. You've been extremely helpful!

T.

 

[dash1b]

You could also use the COALESCE function to achieve a similar result:

dbo.ConcatDesc (@OutreachID int)
returns varchar(1000)
AS
BEGIN

Declare @Str as varchar(1000)

SELECT @Str = COALESCE(@Str + ', ', '') + Descript
FROM myTable
WHERE OutreachID=@OutreachID

RETURN @Str

END

 

[donutman]

Nice touch.
-Karl

 

[dash1b]

Thanks