How to format a SELECT resultset as a comma separated list?
Juan Dent
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.
Razvan Socol
See: http://www.aspfaq.com/show.asp?id=2529
Razvan
Louis Davidson
--
----------------------------------------------------------------------------
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" <rso...@gmail.com> wrote in message
news:1138211041.3...@g43g2000cwa.googlegroups.com...
05ponyGT
news:eibExsdI...@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:)
Dave Frommer
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:ehV76BgI...@TK2MSFTNGP09.phx.gbl...
05ponyGT
target of your arrow...?
Or is my paranoia showing....:)
"Dave Frommer" <an...@spam.com> wrote in message
news:Oy88GPhI...@TK2MSFTNGP11.phx.gbl...
Dave Frommer
"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%23pK8XhI...@TK2MSFTNGP09.phx.gbl...
--CELKO--
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!
05ponyGT
:)
"Dave Frommer" <an...@spam.com> wrote in message
news:O7msVohI...@TK2MSFTNGP14.phx.gbl...
Louis Davidson
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--" <jcel...@earthlink.net> wrote in message
news:1138241669....@z14g2000cwz.googlegroups.com...
![William Stacey [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjUtEanr0b0I_eVupbLDNja5jk09ktJpLjlmTx6Lj38Gm7mCVsZp=s40-c)
William Stacey [MVP]
DECLARE @t table(
Name nvarchar(80),
Age int
);
insert @t SELECT 'one', 1
insert @t select 'two', 2
insert @t select 'three', 3
select dbo.JoinColumn(Name), dbo.JoinColumn(Age)
from @t
Output
--------------------
one,two,three 1,2,3
You can get the JoinColumn aggregate in my Channel9 clr project in the
sandbox.
--
William Stacey [MVP]
"Juan Dent" <Juan...@nospam.nospam> wrote in message
news:4A32E759-3878-46F5...@microsoft.com...
Tony Rogerson
The FOR XML behaviour and [text()] is fully documented in books online.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Dave Frommer" <an...@spam.com> wrote in message
news:Oy88GPhI...@TK2MSFTNGP11.phx.gbl...
Dave Frommer
it was indicated it may be
relying on unintended consequences. ;-)
"Tony Rogerson" <tonyro...@sqlserverfaq.com> wrote in message
news:%23bhV6wk...@tk2msftngp13.phx.gbl...
Tony Rogerson
pre cuppa tea.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Dave Frommer" <an...@spam.com> wrote in message
news:u1$HJJnIG...@tk2msftngp13.phx.gbl...
Tony Rogerson
The FOR XML behaviour and [text()] is fully documented in books online.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"05ponyGT" <nospam@nospam> wrote in message
news:ehV76BgI...@TK2MSFTNGP09.phx.gbl...
05ponyGT
> 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.
Said another way,how is Update,Delete,and a View different than a
Select?The former are bound by the constraits of the model, the later
is not.The only constraint on a Select should be that the rows are unique
as far as key(s) are concerned.
> 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?
On what basis do you agree?
Can you supply a link from the body of database work which argues it's
wrong?:)
Louis Davidson
> Can you supply a link from the body of database work which argues it's
> wrong?:)
If this newsgroup is a body of work then yes. It is frequently said by many
folks (including the one who started me to typing last night,) since it is
easier (and faster) to do this elsewhere. The less we force on the SQL
Server, the better overall concurrency, since locks are held for a shorter
period of time, the CPU can be fullfilling requests, disk reads can happen
faster (rather than wait for data to be formatted,) users can select what
they want, and so on.
Of course when I said shouldn't I meant generally shouldn't, as there are
cases where it is best to just go ahead and do it in SQL, especially when
these are merely intermediate results.
Formatting using SQL or presentation layer code is a tricky thing, I will
admit.
--
----------------------------------------------------------------------------
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)
"05ponyGT" <non...@noname.com> wrote in message
news:%23ZCuygq...@TK2MSFTNGP11.phx.gbl...
> "Louis Davidson" <dr_donts...@hotmail.com> wrote in message
> news:ufmEIwiI...@TK2MSFTNGP09.phx.gbl...
>> 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.
>
s> Said another way,how is Update,Delete,and a View different than a
05ponyGT
>> On what basis do you agree?
>> Can you supply a link from the body of database work which argues it's
>> wrong?:)
>
> If this newsgroup is a body of work then yes.
Ok, I agree.This ng can certainly be quite a piece of work:)
> It is frequently said by many folks (including the one who started me to
> typing last night,) since it is easier (and faster) to do this elsewhere.
> The less we force on the SQL Server, the better overall concurrency, since
> locks are held for a shorter period of time, the CPU can be fullfilling
> requests, disk reads can happen faster (rather than wait for data to be
> formatted,) users can select what they want, and so on.
Interesting.We are on the road to some serious thought here.What you
are really getting at here is the idea that a particular problem can be
solved
merely by how efficient the rdbms has *implemented* the construct(s)
involved.This shifts the focus from *what* one is doing to the *how*
good it can be done.This should be embraced by many as it eleviates
the appropriateness of something and allows approaching problem solving,
at least for retrieval of information, from a purely expedient point of
view.
Of course we do chance opening up pandora's box here since we can
question the implementation of any of the servers construct(s).Of course
everyone realizes there is no free lunch:)
I suspect that there are really a great many in the expedient camp, even
those that don't realize they are in it:)
> Of course when I said shouldn't I meant generally shouldn't, as there are
> cases where it is best to just go ahead and do it in SQL,....
There is no need to take such a ..well ambigious position based on the
above.If there is a problem doing it the database it simply means the
db falls short on how particular construct(s) were implemented.A better
implementation would lead to a thinner client.
> Formatting using SQL or presentation layer code is a tricky thing, I will
> admit.
It's tricky because,like so many other aspects of db work,code has replaced
language.And even tight code is no match for clarity of thought:)
my $.02
Louis Davidson
>> cases where it is best to just go ahead and do it in SQL,....
>
> There is no need to take such a ..well ambigious position based on the
> above.If there is a problem doing it the database it simply means the
> db falls short on how particular construct(s) were implemented.A better
> implementation would lead to a thinner client.
I take an ambiguous opinion because I don't feel as strongly about this as
say: normalization.
When Joe, who threw the first dart, says things like the output of a select
statement violates 1NF, it makes me go hmm. And then come these discussions
(which I generally like, including this one.)
> Interesting.We are on the road to some serious thought here.What you
> are really getting at here is the idea that a particular problem can be
> solved
> merely by how efficient the rdbms has *implemented* the construct(s)
> involved
I guess that is the idea that I was tossing out. I was "brought up" with
the "let the relational language do as much as possible" crowd. We
formatted data, rotated sets, did the output denormalization right in the
SQL code. Why? Because I was pretty dang good at SQL, and most of the
programmers I have worked with in the past liked to get back the data in the
format that they wanted to display it in.
>This should be embraced by many as it eleviates
> the appropriateness of something and allows approaching problem solving,
> at least for retrieval of information, from a purely expedient point of
> view.
I don't know if it should be embraced or not. Part of me hopes that this is
where the Linq project (http://msdn.microsoft.com/netframework/future/linq/)
is going. If we could use SQL-like relational processing in the middle
tier, we could express things in a relational manner, have the presentation
layer have the same kind of 1-statement-translates-into-hundreds power that
the relational engine has (and it could be programmed to decide how and
where to implement data manipulation (possibly based on load one day in the
future?) Get the data, do the join on the server, and then
Then came the "SQL Server can't scale" crowd saying do formatting in the
middle tier. I agree 100% that this is a better approach, but deep down
still want to show everyone out there that I can implement in one line of
relational code what takes thousands of lines in any functional language.
>> Formatting using SQL or presentation layer code is a tricky thing, I will
>> admit.
>
> It's tricky because,like so many other aspects of db work,code has
> replaced
> language.And even tight code is no match for clarity of thought:)
Thought? Do people do this? I usually get beat up for slowing down things
too much when I try to think.
--
----------------------------------------------------------------------------
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)
"05ponyGT" <non...@noname.com> wrote in message
news:%23XY7z%23tIGH...@tk2msftngp13.phx.gbl...
05ponyGT
You should be admired for sharing your thoughts.I can assure you
that your not alone in how you wrestle with these thorny issues.
Have you ever thought just how well your biase(s) are being served?:)
You mention the Linq project.What about extending the processing
power of sql itself, independent of any other framework.If you have
some time check out the commerical product 'Dataphor' @
www.alphora.com
Their work is based on Date's book the 'The Third Manifesto' and his
ideas of what a *true* rdbms should be.You will be introduced to the
D4 sql language which I am sure will just knock you out!:)
If you stop thinking you will let others do it for you.
"Louis Davidson" <dr_donts...@hotmail.com> wrote in message
news:ecDbWUwI...@TK2MSFTNGP09.phx.gbl...
William Stacey [MVP]
| 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"
What is the line of sql again to do that. Am doing report stuff now.
Cheers.
Alexander Kuznetsov
Then came the "SQL Server can't scale" crowd saying do formatting in
the
middle tier. I agree 100% that this is a better approach, but deep
down
still want to show everyone out there that I can implement in one line
of
relational code what takes thousands of lines in any functional
language.
<<
Hi Louis,
I have problems understanding the 100% part. I think that there are
different situations and different priorities. I guess there are quite
a few systems where CPU on the server is not the bottleneck, so saving
CPU cycles on the server does not speed the whole system up, not at
all. More to the point, in many shops we humans are the most precious
resourse, so "one line of relational code" vs. "thousands of lines"
becomes a very important criteria.
Can we agree on 99.9% instead of 100%?
;)
05ponyGT
It's no longer the case in SQL Server 2005, you can do very scalable and
fast concatenation using this feature of the FOR XML...
select distinct type,
( select name + ', ' as [text()]
from sys.objects s2
where s2.type = s1.type
order by s2.name
for xml path( '' )
) as concatentated_name
from sys.objects s1
The FOR XML behaviour and [text()] is fully documented in books online
I think, for the most part, the days of concatenating in the client app are
over; well, for those things the above can resolve that is which is alot of
things I've come across already!
>Tony.
Xml to the rescue:)
For pivoting/xtabs,concatenation and whole lot more check out RAC @
www.rac4sql.net
"William Stacey [MVP]" <william...@gmail.com> wrote in message
news:uM5WxV4I...@TK2MSFTNGP12.phx.gbl...
Hugo Kornelis
05ponyGT
>.
> Then came the "SQL Server can't scale" crowd saying do formatting in the
>.
Does the "SQL Server can't scale" crowd confine itself to *formatting* only?
What about 4 way joins and nested subqueries?Do you ever hear from the
middle tier crowd as it relates to non-formating issues?Do you see the
slippery
slope let alone the contradication(s) here (and don't forget the prior
posts:)?
100% should be reserved for amatuers only:)
> but deep down still want to show everyone out there that I can implement
in one line of
> relational code what takes thousands of lines in any functional language
Now if only technology would catch up to your ego!
Can you see the idea of implementation rearing its head again?
Can you see yourself as an MS sql server xquery programmer:)
More $change
05ponyGT
news:1138397054....@o13g2000cwo.googlegroups.com...
>.
> I have problems understanding the 100% part.
> Can we agree on 99.9% instead of 100%?
I have problems understanding the 99.9% part:)
05ponyGT
news:d74lt1h5bvroq2bqq...@4ax.com...
> Hi William,
>
> http://www.aspfaq.com/show.asp?id=2529
I wonder if the author intends that all the techniques presented
are *kludges* ? :)
Hugo Kornelis
Why don't you ask him? There's a feedback link on his page.
05ponyGT
Thanks.
Done.
--CELKO--
Yes, for two reasons:
1) Newbies will put this crap in the database if you let them via
extensins to SQL. If you wanted to extend DECLARE CURSOR to have a
horrible "screw Codd!!" clause, maybe I could buy it.
2) A VIEW (note upper case). is supposed to be part of the same closure
model that makes RDBMS mathematically sound.
Louis Davidson
> extensins to SQL. If you wanted to extend DECLARE CURSOR to have a
> horrible "screw Codd!!" clause, maybe I could buy it.
Yes, but I don't base what I as a professional developer do on how a newbie
might pervert it. In some ways this statement, I think, gives me more
insight to why you think as you do. Newbies will screw stuff up, invariably
leading to a newbie learning poor habits that as the newbie grows up into an
experienced developer he will still have and think are right. I see that
point, but I would rather move forward to more efficient practices while
still paying proper homage to where these ideas came from, not holding on to
them like they are scriptures, but rather the foundation of greater things
to come (ooh, too philisophical. )
> 2) A VIEW (note upper case). is supposed to be part of the same closure
> model that makes RDBMS mathematically sound.
I also wouldn't probably wouldn't suggest this be represented or stored in a
view. Too costly for the most part. On the other hand, having structures
around for reporting purposes that do some pre-aggregation of data for use
in a reporting tool (think OLAPish) is a very nice thing, but really, even
though the storage might be SQL Server tables, it is not part of a
relational model per ce, and shouldn't be considered a problem
--
----------------------------------------------------------------------------
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--" <jcel...@earthlink.net> wrote in message
news:1138509168.6...@g44g2000cwa.googlegroups.com...