Post reply

Need code to list ID

  • June 17, 2015 at 7:50 am

    #319164

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

    000001

    000002

    ...

    000100

    How to code to output like below?

    000001, 000002,...000100

  • June 17, 2015 at 8:04 am

    #1806082

    Something like this, assume memberid is varchar column.

    DECLARE @IDList VARCHAR(MAX)

    SELECT

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

    FROM your_table_name

    PRINT @IDList

  • June 17, 2015 at 10:04 am

    #1806138

    It works. Thanks

  • June 17, 2015 at 8:49 pm

    #1806248

    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

  • June 17, 2015 at 10:32 pm

    #1806254

    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)
    Intro to Tally Tables and Functions

  • June 18, 2015 at 6:25 am

    #1806336

    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

  • June 18, 2015 at 6:33 am

    #1806338

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

    dim _0000001, _0000002...

  • June 18, 2015 at 6:38 am

    #1806342

    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.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

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

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