Need code to list ID

Question

Post reply

Need code to list ID

adonetok

SSCrazy Eights

Points: 8395
More actions
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

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

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

Andrei Hetel Old Hand Points: 393 More actions · Answer 1

Something like this, assume memberid is varchar column.

DECLARE @IDList VARCHAR(MAX)

SELECT

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

FROM your_table_name

PRINT @IDList

adonetok SSCrazy Eights Points: 8395 More actions · Answer 2

adonetok

SSCrazy Eights

Points: 8395

June 17, 2015 at 10:04 am

#1806138

It works. Thanks

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

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)

Y.B. SSChampion Points: 11649 More actions · Answer 5

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

adonetok SSCrazy Eights Points: 8395 More actions · Answer 6

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

dim _0000001, _0000002...

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

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