Concatenation Cursor

  • sanbornd

    SSC-Addicted

    Points: 412

    Comments posted to this topic are about the item Concatenation Cursor

  • Joy Smith San

    SSC-Insane

    Points: 24877

    It's a very good question. Realy useful.

  • This was removed by the editor as SPAM

  • ziangij

    SSCertifiable

    Points: 6934

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

  • Open Minded

    SSCommitted

    Points: 1842

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

  • Clive Chinery

    SSCrazy

    Points: 2503

    An excellent question! Thank you for posting it!

  • Richard M Karpel

    Ten Centuries

    Points: 1221

    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:

  • WayneS

    SSC Guru

    Points: 95341

    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

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    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

  • Juan de Dios

    Old Hand

    Points: 335

    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???

  • This was removed by the editor as SPAM

  • tmacs33

    SSC Eights!

    Points: 975

    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!!

  • Lawrence M. Meklemburg

    Ten Centuries

    Points: 1217

    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" .

  • Ron McCullough

    SSC Guru

    Points: 63877

    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]

  • dun

    Say Hey Kid

    Points: 708

    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 43 total)

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