Post reply

Concatenation Cursor

  • February 15, 2010 at 9:59 pm

    #216652

    Comments posted to this topic are about the item Concatenation Cursor

  • February 15, 2010 at 10:15 pm

    #1119068

    It's a very good question. Realy useful.

  • February 15, 2010 at 10:28 pm

    This was removed by the editor as SPAM

  • February 15, 2010 at 11:35 pm

    #1119081

    thanks, a very good question ! got to learn something new today.:-)

  • February 16, 2010 at 1:22 am

    #1119099

    I have never been so glad to be wrong! I think some of my SPs with temp cursors may meet an end.

  • February 16, 2010 at 1:50 am

    #1119107

    An excellent question! Thank you for posting it!

  • February 16, 2010 at 4:08 am

    #1119158

    This is a statement I use all the time in creating Dynamic SQL strings. It is great for use with pivot tables. Something I have been using for a while.:cool:

    Rick Karpel

  • February 16, 2010 at 5:33 am

    #1119194

    Just remember that without an order by clause, there is no guaranteed order. The correct answer for this could have been just as easily "Bill, Mark, John".

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • February 16, 2010 at 5:39 am

    #1119199

    Wow, I never knew about this! I've got a few SPs to clean up this morning! 🙂

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • February 16, 2010 at 5:59 am

    #1119215

    This script is very useful when all the rows are not NULL, but if you modify the insert of Mark with NULL, the script only returns Bill.

    insert into @table values (4, NULL,'M')

    Result: Bill

    Where John goes???

  • February 16, 2010 at 7:08 am

    This was removed by the editor as SPAM

  • February 16, 2010 at 7:14 am

    #1119263

    Nice question. I guessed at the answer but lucked out.

    I already have a use for this in a couple of procedures today. PERFECT TIMING!!

  • February 16, 2010 at 7:15 am

    #1119266

    As a couple of comments stated, the names may not come back in the order you want. On the same server, I ran this query at different times and got "John, Mark, Bill", "John, Bill, Mark" and "Bill, John, Mark" .

  • February 16, 2010 at 7:40 am

    #1119286

    Juan de Dios (2/16/2010)

    --------------------------------------------------------------------------------

    This script is very useful when all the rows are not NULL, but if you modify the insert of Mark with NULL, the script only returns Bill.

    insert into @table values (4, NULL,'M')

    Result: Bill

    Where John goes???

    stewartc-708166

    To cate for this, an aditional clause should be added to the predicate, viz:

    AND Name IS NOT NULL

    In addition or an alternative to stewartc excellent suggestion, you can in effect negate the presence of the NULL by using an ORDER BY [name] clause

    More interesting try the code with a BLANK name, without the ORDER BY clause it appears in the list of names as , ,

    - with the ORDER BY clause it appears as a leading comma before Bill.

    sanbornd

    An EXCELLENT QOD thanks for devising it and submitting, may I encourage you to submit additional QODs

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • February 16, 2010 at 8:25 am

    #1119310

    Here's another alternative using STUFF and FOR XML PATH that does not require a parameter (I believe this is from an article from SSC. If I could have found the URL I would have included it in this post in order to give the appropriate credit.)

    SELECT STUFF((SELECT ', ' + name

    FROM @table

    WHERE gender = 'M'

    FOR XML PATH('')), 1, 2, '')

Viewing 15 posts - 1 through 15 (of 42 total)

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