Home Forums Article Discussions Article Discussions by Author Discuss content posted by Wayne Sheffield Creating a comma-separated list (SQL Spackle) RE: Creating a comma-separated list (SQL Spackle)

  • October 30, 2012 at 8:51 am

    #1554202

    chris.westgate (10/30/2012)

    Great solution Wayne, thank you...

    But for me, here's a tiny spanner in the works.

    The column that I need to produce a delimited list from is an int column - and I need the list ordered by integer. However, trying to concatenate ',' with an int of course results in a conversion error trying to convert ',' to an int... BUT, if I cast my int value as a varchar, of course, it then produces a list that runs 1,10,11, 12, 2, 20... etc...

    Any ideas please?

    Cheers

    Chris

    You've basically just answered you own question. The only way to concatenate INTs will be to convert them. Just add an ORDER BY to you XML based SELECT and Bob's your uncle.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)