Combining results in Comma delimitered strings

I know this has been addressed before but I can't find it...
I have a table with with a column called PersonId. I want a query that will return all the PersonId's as a comma delimited string...
Anyone able to help?DECLARE @.commadelimitedthisisanannoyingstringname VARCHAR(8000)

SELECT @.thatstringupthereyouregoingtonottypethis = ''

SELECT @.String = PersonId + ', ' + @.String
FROM Person

SELECT LEFT(@.String, LEN(@.String)-1)

or something like that|||Thanks, that seems to work,... I didn't think it would but some how it does...|||Thanks, that seems to work,... I didn't think it would but some how it does...

It should have worked. :) Why would you not think it would, just curious.|||I would have thought that for that structure to work there would have had to be some sort of loop or something...

I thought I had seen it done using some other function like coalesce or something but when I read the help it didn't look right...

Hmmm,... actually thinking about it now it makes sense... basically it ignores what if selected in the table select and just selects the final built up string...

Seems a little inefficient, is there a better way??|||Actually, if you do a search here or on www.sqlteam.com, you'll find all kinds of ways to do it. :) You can do it with a function using COALESCE. I think for a single string though, this one is pretty efficient.|||Come on, derrick, NOBODY here thought that would work the first time we saw it months back! It's probably one of the most popular tricks on the forum!|||?? You're joking right? I have scripts going way back using that.|||I know that folks use the living stuff out of this construct, but it shouldn't work. It actually violates the SQL-92 standard, since the column value is derived iteratively. My guess is that once the standards committee realizes that this flaw exists in a widely used SQL dialect, they'll add a test for it to the test suite and shortly thereafter Microsoft will either eliminate the behavior or make it switch dependant.

-PatP|||Yup, it's a nifty little thing that has gotchya's people don't think about, and as always tend to overuse it. Like with everything else that is non-standard, when it comes to an end, a lot of these guys are gonna be screaming bloody hell, but it's only because they were lazy at the beginning, though not lazy enough to prevent it right there, and have a beer ;)|||Yeah, but it is lots of fun to watch them running around, screaming to the four winds when the database engine behavior is fixed and their code breaks. As you are fond of observing, those who ignore history are doomed to repeat it!

-PatP|||Okie then guys,... what is the right way to do it? To be honest I have actually implemented a different solution as I didn't get a reply in time so it isn't going to cause me any problems but I would be interested in knowing if there is a proper way or not...|||The proper way would be to reorg your front-end and not display a comma-separated string or PersonIDs, but rather display a grid of such, where you can also include PersonName and other pertinent information. It's a christomatic drill-down approach where you see the header, click on Detail button and get everything that is associated with the highlighted header record. This IS the right approach, without trying to trick life and SQL engine. But if you continue, what are you gonna tell your users when they start getting partial PersonID at the end of the string, because the total length of the returned string exceeded 8000 character?|||Well that answer gains zero points for usefullness...

No offense meant. For the purposes of the project I am doing I need a comma delimited string of all the id's. This is not for displaying but for internal processing. A string may not be the best option but at this point it time is the most flexible and the project at hand. The truth is that there is no front in for the problem I am working on. It's a logic problem that I want to solve. Once it is solved I might decide that it is no use or I might decide that there is a front end requirement but at this point there is not.

Now while I agree that I probably don't want SQL to return a comma delimited string because it is a misrepresentation of the actual data I do want to know what is the best way of getting sql to return a comma delimited string so I know what I am talking about when I rule it out as an option.|||No matter how the string is getting formed, it's still limited to the total length of 8000 character (wonder how many times I tried to hint it?:rolleyes: )

Why don't you tell us what the app is for, and why it needs a comma-delimited listing of PersonIDs? Someone (maybe me) would be able to come up with an alternative, hey?! ;)|||I think the answer to your hint question is once (in this post anyway).

The string is used for determining individual branches in a family tree. The processing is all done using asp/vbscript. An array might be better then a string but because you are shuffling data into and out of other stings/arrays at this point a string is the best contruct to test the theory and determine it's usefulness.

At this point conversion of a recordset to a string is simplier then a recordset to an array.

The actually processing overhead of using a string is probably higher but until I determine the usefulness of the function their is little point moving to an array which would be more complex at this point.

Before you ask, yes, you are stuck with asp and vbscript. It can't go to a vb component and it can't upgrade to asp.Net.

Knock yourself out.|||Hey-hey-hey, with that kind of attitude, YOU knock yourself out, not me, alright?! I was just trying to help you (which is why I choose to post here), but as you MIGHT have noticed, it's NOT my problem. If you think you're better than that, - KNOCK YOURSELF OUT, do yourself a favor.

EDITED: and BTW, I hinted on VARCHAR limitation at least twice. I simply didn't think that such an obvious thing as this needs to be mentioned...Obviously it does, hey?!|||*sigh* why is it that people are easily offended...

Look, I appreciate what you are saying and I appreciate your input, the "knock yourself out" line is a standard line from where I come from to say "go for it and good luck". I'm sorry it offended you.|||Fair dinkum. :cool:|||WHAT WAS WRONG WITH THIS ANSWER? Tel me straight, please, I I promise I will take a serious consideration over how I post my answers...The proper way would be to reorg your front-end and not display a comma-separated string or PersonIDs, but rather display a grid of such, where you can also include PersonName and other pertinent information. It's a christomatic drill-down approach where you see the header, click on Detail button and get everything that is associated with the highlighted header record. This IS the right approach, without trying to trick life and SQL engine. But if you continue, what are you gonna tell your users when they start getting partial PersonID at the end of the string, because the total length of the returned string exceeded 8000 character?|||What's wrong with the answer is it doesn't actually answer the question.

The question is, is there a proper way to create a comma delimited string in the manner described within SQL (and if there is, what is it).

It is not, how should I build my application so that I do not have to use a comma delimited string, nor is it what restrictions will I put in place by using a comma delimited string.

I'd be quite happy if some one answered "there is no 'proper' way to generate a comma delimited string from sql". I am aware that there are going to be limitations, but in some cases (not this one) the limitation may not come into play (depending on data and data structures).|||Hey, have it your way. I hope the the limitation pointed out is not gonna be a problem for you ;)|||Eventually it would be, but since this was only to test a theory it's not going to and like I said previously I have actually gone about it a different way, but it would still be interesting to know if there is a "proper" way to do it...

An example of where it might be useful is when you want a comma delimited list of the months that are stored in a particular table. You can be certain that the length of the string will not exceed 8000 characters...

I'm not sure where else it might be useful but there are bound to be others.

Thanks for your input.sqlsql