How to format a SELECT resultset as a comma separated list?

Hi,
I have the following need.
DECLARE @.Sql varchar(3000)
SET @.Sql = 'SELECT ' +
(SELECT name FROM pbajunio.sys.all_columns
WHERE object_id = object_id(@.TableName,'U')
AND system_type_id in (35,99,167,175,231,239))
+ ' FROM ' + @.TableName
--
I would like the inner SELECT (which produces a one column resultset) to be
formatted as a list separated by commas whose elements are the values of the
column of each row in the resultset.
Is this possible?
Thanks in advance,
Juan Dent, M.Sc.Hello, Juan
See: http://www.aspfaq.com/show.asp?id=2529
Razvan|||Awesome article! I hadn't seen the FOR XML trick. That is very neat.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138211041.333378.270990@.g43g2000cwa.googlegroups.com...
> Hello, Juan
> See: http://www.aspfaq.com/show.asp?id=2529
> Razvan
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eibExsdIGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Awesome article! I hadn't seen the FOR XML trick. That is very neat.
Yes,interesting.It will also be interesting to see what 'those in the know'
who have ranted that such things should be in done in the client have
to say about this:)Some will find a delicious irony in that we are
talking xml in a relational database engine let alone this particular
(unentended consequence I would bet:) solution:)
www.rac4sql.net|||Ironic, No.
Interesting, Yes
That anyone would depend on "Unintended Consequences" and other
un-documented side effects in code that can easily break after the next
Service Pack, is, very interesting :-)
"05ponyGT" <nospam@.nospam> wrote in message
news:ehV76BgIGHA.3492@.TK2MSFTNGP09.phx.gbl...
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:eibExsdIGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Yes,interesting.It will also be interesting to see what 'those in the
> know'
> who have ranted that such things should be in done in the client have
> to say about this:)Some will find a delicious irony in that we are
> talking xml in a relational database engine let alone this particular
> (unentended consequence I would bet:) solution:)
> www.rac4sql.net
>|||Hmm...perhaps you should clarify just who is the intended
target of your arrow...?
Or is my paranoia showing...:)
"Dave Frommer" <anti@.spam.com> wrote in message
news:Oy88GPhIGHA.1836@.TK2MSFTNGP11.phx.gbl...
> Ironic, No.
> Interesting, Yes
> That anyone would depend on "Unintended Consequences" and other
> un-documented side effects in code that can easily break after the next
> Service Pack, is, very interesting :-)
>
> "05ponyGT" <nospam@.nospam> wrote in message
> news:ehV76BgIGHA.3492@.TK2MSFTNGP09.phx.gbl...
>|||The intended target is:
"anyone who would depend on "Unintended Consequences" and other
un-documented side effects in code that can easily break after the next
Service Pack"
<grin>
"05ponyGT" <nospam@.nospam> wrote in message
news:O%23pK8XhIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> Hmm...perhaps you should clarify just who is the intended
> target of your arrow...?
> Or is my paranoia showing...:)
> "Dave Frommer" <anti@.spam.com> wrote in message
> news:Oy88GPhIGHA.1836@.TK2MSFTNGP11.phx.gbl...
>|||Please read at least one book -- ANY BOOK -- on RDBMS before you code.
Go to one of the first chapters and learn what FIRST nORMAL FORM (1NF)
and why it is the very foundations of SQL.
Yes, there are stinking dirty kludges to violate 1NF and the entire
concept of tiered architecture. hey, if you really want to mess up
everything and slow down your code, add XML to the mix!|||Trying to kill 2 birds with one stone....cute.
:)
"Dave Frommer" <anti@.spam.com> wrote in message
news:O7msVohIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> The intended target is:
> "anyone who would depend on "Unintended Consequences" and other
> un-documented side effects in code that can easily break after the next
> Service Pack"
> <grin>
> "05ponyGT" <nospam@.nospam> wrote in message
> news:O%23pK8XhIGHA.1728@.TK2MSFTNGP09.phx.gbl...
neat.
>|||Would you really feel it a violation of 1nf to return data this way? Not
store it (that would be a "sin") but just to view it.
I completely agree that this kind of thing shouldn't be done in the data
tier, but where would you draw the line? Are aggregates wrong? Should we
not be summing data in SQL? Or adding? Should SQL simply be used to store
data? I mean, why is it always wrong to do any kind of data manipulation
here where it is easy to do in a few lines of (mostly) relational
programming?
For starters, say we have the following set (the first two columns are
functionally dependent on one another, and there would likely be more
columns):
1, 200, "Joe, Jerry, Jimmy, JoeBob",
2, 300, "Fred, Filbert"
Is it not more efficient to do this with one line of SQL code, instead of
returning:
1, 200, "Joe"
1, 200, "Jerry"
1, 200, "Jimmy"
1, 200, "JoeBob"
2, 300, "Fred"
2, 300, "Filbert"
Or two resultsets and have to write iterative code that iterates over the
set?
The first format is a great way to return data to a report writer, because
the second set can be annoying (because even when grouping this will be 4
rows for 1, and 2 rows for 2. So if 200 happened to be a number that you
had to do math on, you would either have to do some sort of division to
change it to 1, 50 on each row, or something else.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138241669.307414.84260@.z14g2000cwz.googlegroups.com...
> Please read at least one book -- ANY BOOK -- on RDBMS before you code.
> Go to one of the first chapters and learn what FIRST nORMAL FORM (1NF)
> and why it is the very foundations of SQL.
> Yes, there are stinking dirty kludges to violate 1NF and the entire
> concept of tiered architecture. hey, if you really want to mess up
> everything and slow down your code, add XML to the mix!
>