Unable to Create Concatenated String

Question

Unable to Create Concatenated String

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

vivet Old Hand Points: 372 More actions · Answer 1

Initialize the PeriodArray Variable with blank ('')

Further, why are you using a cursor? I think you could relatively easy refactor into a funtion, which has far better performance that a cursor.

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 2

vivets (11/3/2008)
Initialize the PeriodArray Variable with blank ('')
Further, why are you using a cursor? I think you could relatively easy refactor into a funtion, which has far better performance that a cursor.

Hi Vivets, thanks for your help it works as expected now. I'll look into using a function instead. I knew that there would probably be a better method than using a cursor but just where was the issue.

Thanks

David

Sergiy SSC Guru Points: 110209 More actions · Answer 3

select @periodArray = ISNULL(@periodArray + ',', '') + Period

from @myTable

select @periodArray

Much less typing.

🙂

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Lynn Pettis

SSC Guru

Points: 442467

November 3, 2008 at 9:48 pm

#893411

Have to agree with Sergiy on this one.

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 5

Sergiy (11/3/2008)
select @periodArray = ISNULL(@periodArray + ',', '') + Period from @myTable select @periodArray
Much less typing.
🙂

Sergiy, amazing! Can you explain how it works though?! In the past I've used ISNULL to simply replace a value if it is null. What I don't understand is how the results from the table were converted into a concatenated string.

Thanks

David

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 6

David (11/4/2008)
Sergiy, amazing! Can you explain how it works though?! In the past I've used ISNULL to simply replace a value if it is null. What I don't understand is how the results from the table were converted into a concatenated string.
Thanks
David

When you declare a variable it's initial value is NULL.

NULL used with any operator (such as concatenation, '+') returns NULL.

This both explains why your original cursor solution returned NULL (it just kept concatenating NULL+something and getting NULL) and why Sergiy's solution works (on returning the first row, NULL+',' is NULL so an empty string is used; this is concatenated with period and all subsequent rows return (something+',')+period, i.e. add a comma and the next value to what we already have).

Derek