Need code to list ID

  • One column (memberID) store about 100 member ID like below

    000001

    000002

    ...

    000100

    How to code to output like below?

    000001, 000002,...000100

  • Something like this, assume memberid is varchar column.

    DECLARE @IDList VARCHAR(MAX)

    SELECT

    @IDList = COALESCE(@IDList + ',', '') + memberID

    FROM your_table_name

    PRINT @IDList

  • It works. Thanks

  • Another way...

    DECLARE @IDList VARCHAR(MAX);

    WITH yourtable AS

    (

    SELECT memberID FROM (VALUES ('001'),('002'),('003')) t(memberID)

    )

    SELECT @IDList = STUFF ((SELECT ','+memberID FROM yourtable FOR XML PATH('')),1,1,'');

    PRINT @IDList;

    GO

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • adonetok (6/17/2015)


    One column (memberID) store about 100 member ID like below

    000001

    000002

    ...

    000100

    How to code to output like below?

    000001, 000002,...000100

    Your turn, please. Why do you need the data in this format? What is the business reason for it? I'm just curious.

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

  • Jeff Moden (6/17/2015)


    adonetok (6/17/2015)


    One column (memberID) store about 100 member ID like below

    000001

    000002

    ...

    000100

    How to code to output like below?

    000001, 000002,...000100

    Your turn, please. Why do you need the data in this format? What is the business reason for it? I'm just curious.

    I was thinking the exact same thing. Let's hope membership doesn't go up! :Whistling:


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • In asp.net project, I need to declare variables using all these IDs. Like,

    dim _0000001, _0000002...

  • adonetok (6/18/2015)


    In asp.net project, I need to declare variables using all these IDs. Like,

    dim _0000001, _0000002...

    I don't know the specifics of your project, but why not just use an array? If you make it an array of your own structure, you can have the key in one element and the rest of your data in another.

    If you're just looking for a table, you could use the Data.DataTable data type instead.

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

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