concatenating of column data in sql.Faq2559,written by a very
knowledgable MVP,is often cited as a reference for this
problem's solution.This problem is interesting as it has
spurred debate on a number of issues including client vs. server,
static vs. dynamic sql and the nature of the sql solutions.
Perhaps there is even more of interest here between the code.
For example, the author starts out with the following:
>This question is asked quite frequently. People want to take a column like
>this:
Color
--
red
orange
blue
green
And return a resultset like this:
Colors
--
>red,orange,blue,green
by stating:
>This isn't exactly relational...
Has anyone wondered exactly what 'isn't exactly relational' means?
I assume I'm safe in assuming he is referring to the resultset.
Which part is relational and which part is not relational?(Putting
aside the idea whether it even makes sense to refer to a resultset
as being 'relational').Is it the comma's that cause the problem?
What about :
1,100,100
which could be money?Has anyone said this is not exactly relational?
Or John's Pizza Hut
Is the apostrophe objectionable?
Nothing wrong with:
Lastname+','+Firstname
is there?
Or does the fuzzy part of relational come from the fact that the
result came from a single column?This is interesting since 2V logic,
whether a result is relational or quasi-relational,is predicated on
'where' the result came from.So we don't know if '1,100,100' is
relational or almost relational unless we know 'exactly' where it
came from.What if we don't know where it came from?Is the result
null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented
that a 'pure' sql solution to concatenation 'would never destroy 1NF
by building a list structure..'.Sounds real good but it is Bizarre!
Perhaps he's suggesting a new type of check constraint.The 99.9%
ivory soap constraint:)
Anyone on what this phrase means?
Here's another one.The author states:
'SQL Server 2005 adds some interesting options for this kind of
non-relational
query.'
What is a 'non-relational' query?Is it a query that produces a result
that isn't exactly relational?Is it a query that some dislike?You can
see where this is going:)
(Note that another MVP referred to the xml solution as a 'set based
solution'.
but that is a subject for another thread:)
Anyone on what a non-relational query is?
It's obviously easier to code this stuff than it is to talk about it.
Which would explain why sql remains such a mystery to so many.Be that
as it may, to try to get some clarity in all the haze is much more
interesting,challenging and fun than coding it.You just gotta love
the glass:)
(The author of the faq is to be recognized for his great contributions
and assistance to the server community.His words have impact and the
bar is set high in such a case).
musings from
www.rac4sql.netHi
In my opinion the list of contacanation values ( if you want to store it in
the table) would destroy 1NF
However , you can generate a report using this technique but it would be
better if we build such reports on the client side .
One reason is that you cannot guarantee the ordering of the result set
> Or John's Pizza Hut
> Is the apostrophe objectionable?
> Nothing wrong with:
> Lastname+','+Firstname
> is there?
When you design the database you would like to have two separated colums
fo Firstname and Lastname as well.
In result set as a report you may contacanate these columns depends upon
requirements
I have my doubt i have answered your question , so It is my opinion only
"05ponyGT" <noname@.overwood.com> wrote in message
news:Or7TnghLGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Lately there seems to be quite a few posts regarding the
> concatenating of column data in sql.Faq2559,written by a very
> knowledgable MVP,is often cited as a reference for this
> problem's solution.This problem is interesting as it has
> spurred debate on a number of issues including client vs. server,
> static vs. dynamic sql and the nature of the sql solutions.
> Perhaps there is even more of interest here between the code.
> For example, the author starts out with the following:
>
> Color
> --
> red
> orange
> blue
> green
> And return a resultset like this:
> Colors
> --
> by stating:
> Has anyone wondered exactly what 'isn't exactly relational' means?
> I assume I'm safe in assuming he is referring to the resultset.
> Which part is relational and which part is not relational?(Putting
> aside the idea whether it even makes sense to refer to a resultset
> as being 'relational').Is it the comma's that cause the problem?
> What about :
> 1,100,100
> which could be money?Has anyone said this is not exactly relational?
> Or John's Pizza Hut
> Is the apostrophe objectionable?
> Nothing wrong with:
> Lastname+','+Firstname
> is there?
> Or does the fuzzy part of relational come from the fact that the
> result came from a single column?This is interesting since 2V logic,
> whether a result is relational or quasi-relational,is predicated on
> 'where' the result came from.So we don't know if '1,100,100' is
> relational or almost relational unless we know 'exactly' where it
> came from.What if we don't know where it came from?Is the result
> null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented
> that a 'pure' sql solution to concatenation 'would never destroy 1NF
> by building a list structure..'.Sounds real good but it is Bizarre!
> Perhaps he's suggesting a new type of check constraint.The 99.9%
> ivory soap constraint:)
> Anyone on what this phrase means?
> Here's another one.The author states:
> 'SQL Server 2005 adds some interesting options for this kind of
> non-relational
> query.'
> What is a 'non-relational' query?Is it a query that produces a result
> that isn't exactly relational?Is it a query that some dislike?You can
> see where this is going:)
> (Note that another MVP referred to the xml solution as a 'set based
> solution'.
> but that is a subject for another thread:)
> Anyone on what a non-relational query is?
> It's obviously easier to code this stuff than it is to talk about it.
> Which would explain why sql remains such a mystery to so many.Be that
> as it may, to try to get some clarity in all the haze is much more
> interesting,challenging and fun than coding it.You just gotta love
> the glass:)
> (The author of the faq is to be recognized for his great contributions
> and assistance to the server community.His words have impact and the
> bar is set high in such a case).
> musings from
> www.rac4sql.net
>
>
>|||Anybody wanting concatenation like that and is using SQL Server 2005 should
be using the FOR XML in there first instance, it scales, is fully documented
in books online and works really well.
> One reason is that you cannot guarantee the ordering of the result set
Yes you can in SQL Server 2005!
Use the FOR XML syntax...
declare @.colour table (
name nvarchar( 50 ) not null primary key
)
insert @.colour ( name ) values( 'Red' )
insert @.colour ( name ) values( 'Green' )
insert @.colour ( name ) values( 'Blue' )
select *
from @.colour
-- Now concatenated
select (
select name + ', ' as [text()]
from @.colour
order by name
for xml path( '' ) ) as colour_name_concatentated
-- More powerful grouping example
select distinct type,
( select name + ', ' as [text()]
from sys.objects s2
where s2.type = s1.type
order by s2.name desc
for xml path( '' ) ) as concatenated_names
from sys.objects s1
order by type desc
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ODn2yrhLGHA.2912@.tk2msftngp13.phx.gbl...
> Hi
> In my opinion the list of contacanation values ( if you want to store it
> in the table) would destroy 1NF
> However , you can generate a report using this technique but it would be
> better if we build such reports on the client side .
> One reason is that you cannot guarantee the ordering of the result set
>
> When you design the database you would like to have two separated colums
> fo Firstname and Lastname as well.
> In result set as a report you may contacanate these columns depends upon
> requirements
> I have my doubt i have answered your question , so It is my opinion only
>
>
> "05ponyGT" <noname@.overwood.com> wrote in message
> news:Or7TnghLGHA.2036@.TK2MSFTNGP14.phx.gbl...
>|||Probably best reading this http://en.wikipedia.org/wiki/Relational_model, it
gives a good explanation.
As for the concatenation being non-relational, it depends on what you are
doing.
If 'red, green, blue' where stored in a description column does that then
mean that column is now non-relational?
My take is that just because we merge rows in this way does not break normal
form because the new data becomes something else, its no longer a set of
colours its a set of descriptors, probably for data export or for
scalability reasons so the client or middle tier doesn't have to do the
work.
Now consider the SQL in SQL Server 2005 that does concatenation...
select distinct type,
( select name + ', ' as [text()]
from sys.objects s2
where s2.type = s1.type
order by s2.name desc
for xml path( '' ) ) as concatenated_names
from sys.objects s1
order by type desc
This still yeilds a set of data, that set can be used in other queries,
inserted - whatever!
I hear arguments that this type of thing should be done in the client
application, imho thats just plain wrong now, the FOR XML extensions are
extremely powerful and scale extremely well, and the code is very succinct
and maintainable. It would take dozens of lines of code to do this client
side, which is one maintanence and development overhead, but the other
overhead is that you need to do it in each application that wants that data,
that might be ASP.NET in one instance, RS in another etc...
This leads me into the debate on where do you put business logic now, Jim
Gray has done some really good research on this and his take is that the SQL
Server (not the database because SQL Server 'the product' is more than just
a data store) is becoming the central place to hold logic that can be shared
via multitudes of applications - it saves you coding a middle tier and
having all that infrastructure. SQL Server can be a web service now, has CLR
etc... The biggest single thing that people will need to change their
thinking on is should SQL Server be exposed that close to the internet say,
there are ways to proxy stuff - but its a discussion.
Anyway, I'm rambling completely off topic.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"05ponyGT" <noname@.overwood.com> wrote in message
news:Or7TnghLGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Lately there seems to be quite a few posts regarding the
> concatenating of column data in sql.Faq2559,written by a very
> knowledgable MVP,is often cited as a reference for this
> problem's solution.This problem is interesting as it has
> spurred debate on a number of issues including client vs. server,
> static vs. dynamic sql and the nature of the sql solutions.
> Perhaps there is even more of interest here between the code.
> For example, the author starts out with the following:
>
> Color
> --
> red
> orange
> blue
> green
> And return a resultset like this:
> Colors
> --
> by stating:
> Has anyone wondered exactly what 'isn't exactly relational' means?
> I assume I'm safe in assuming he is referring to the resultset.
> Which part is relational and which part is not relational?(Putting
> aside the idea whether it even makes sense to refer to a resultset
> as being 'relational').Is it the comma's that cause the problem?
> What about :
> 1,100,100
> which could be money?Has anyone said this is not exactly relational?
> Or John's Pizza Hut
> Is the apostrophe objectionable?
> Nothing wrong with:
> Lastname+','+Firstname
> is there?
> Or does the fuzzy part of relational come from the fact that the
> result came from a single column?This is interesting since 2V logic,
> whether a result is relational or quasi-relational,is predicated on
> 'where' the result came from.So we don't know if '1,100,100' is
> relational or almost relational unless we know 'exactly' where it
> came from.What if we don't know where it came from?Is the result
> null?This is Bizzare!:)Yet the emperor himself (Mr. Celko) has commented
> that a 'pure' sql solution to concatenation 'would never destroy 1NF
> by building a list structure..'.Sounds real good but it is Bizarre!
> Perhaps he's suggesting a new type of check constraint.The 99.9%
> ivory soap constraint:)
> Anyone on what this phrase means?
> Here's another one.The author states:
> 'SQL Server 2005 adds some interesting options for this kind of
> non-relational
> query.'
> What is a 'non-relational' query?Is it a query that produces a result
> that isn't exactly relational?Is it a query that some dislike?You can
> see where this is going:)
> (Note that another MVP referred to the xml solution as a 'set based
> solution'.
> but that is a subject for another thread:)
> Anyone on what a non-relational query is?
> It's obviously easier to code this stuff than it is to talk about it.
> Which would explain why sql remains such a mystery to so many.Be that
> as it may, to try to get some clarity in all the haze is much more
> interesting,challenging and fun than coding it.You just gotta love
> the glass:)
> (The author of the faq is to be recognized for his great contributions
> and assistance to the server community.His words have impact and the
> bar is set high in such a case).
> musings from
> www.rac4sql.net
>
>
>|||Could it be that in IT 'I code therefore I am' has replaced 'I think
therefore I am':)
We need more knowledgable people 'rambling' about the things we often
take for granted.I hope you ramble more for all our sakes:)
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uRLVZqiLGHA.500@.TK2MSFTNGP15.phx.gbl...
> Probably best reading this http://en.wikipedia.org/wiki/Relational_model,
> it gives a good explanation.
> As for the concatenation being non-relational, it depends on what you are
> doing.
> If 'red, green, blue' where stored in a description column does that then
> mean that column is now non-relational?
> My take is that just because we merge rows in this way does not break
> normal form because the new data becomes something else, its no longer a
> set of colours its a set of descriptors, probably for data export or for
> scalability reasons so the client or middle tier doesn't have to do the
> work.
> Now consider the SQL in SQL Server 2005 that does concatenation...
> select distinct type,
> ( select name + ', ' as [text()]
> from sys.objects s2
> where s2.type = s1.type
> order by s2.name desc
> for xml path( '' ) ) as concatenated_names
> from sys.objects s1
> order by type desc
> This still yeilds a set of data, that set can be used in other queries,
> inserted - whatever!
> I hear arguments that this type of thing should be done in the client
> application, imho thats just plain wrong now, the FOR XML extensions are
> extremely powerful and scale extremely well, and the code is very succinct
> and maintainable. It would take dozens of lines of code to do this client
> side, which is one maintanence and development overhead, but the other
> overhead is that you need to do it in each application that wants that
> data, that might be ASP.NET in one instance, RS in another etc...
> This leads me into the debate on where do you put business logic now, Jim
> Gray has done some really good research on this and his take is that the
> SQL Server (not the database because SQL Server 'the product' is more than
> just a data store) is becoming the central place to hold logic that can be
> shared via multitudes of applications - it saves you coding a middle tier
> and having all that infrastructure. SQL Server can be a web service now,
> has CLR etc... The biggest single thing that people will need to change
> their thinking on is should SQL Server be exposed that close to the
> internet say, there are ways to proxy stuff - but its a discussion.
> Anyway, I'm rambling completely off topic.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "05ponyGT" <noname@.overwood.com> wrote in message
> news:Or7TnghLGHA.2036@.TK2MSFTNGP14.phx.gbl...
>|||It depends on how accurately you want to define the operation as relational.
In certain contexts, many general SQL operations are deemed relational while
in some other formal discourses they are not.
SQL's concept of types/domains is a bit strange. All it provides is exposed
representations using primitive built-in datatypes that are subject to
different user interpretations.
As we all know, a type is a defined set of values as associated operators on
those values -- this concept on associated operators is important. For
instance, consider some commonly understood types like HEIGHT, QUANTITY,
TEMPERATURE, DISTANCE etc. In a language like SQL that cannot adequately
support such types/domains, the user is forced to use built-in datatypes
like say NUMERIC or INTEGER. Thus, while multiplying values of TEMPERATURE
to that of HEIGHT is meaningless, a system where these are all represented
using INTEGER datatype would allow such multiplications.
Due to insufficient type support in such systems, the type specificity and
validity of such operations is often maintained and external to the DBMS;
and often subject to different interpretations.
With a resultset of concatenated values, it boils down to whether each
column value in the resultset is treated as a scalar or not. Technically,
all values in SQL are scalar; however the user interpretations of these
values are not. Thus the value "red,orange,blue,green", while a VARCHAR
value -- technically scalar in SQL -- might be interpreted by the user as a
list of 4 smaller strings that represent color values arranged in some
predetermined order.
( Another perspective is that concatenation of row values as a "relational"
operation relies on essential ordering of values which relational model
explicitly prohibits. Thus one could question the relevance of the results
being outside the realms of relational model. So should the results be
"red,orange,blue,green" or "orange,blue,red,green" or
"blue,green,red,orange" or something else? )
Coming to the questions in your post,
Something that is not exactly specific to theory of relational data
management.
Precisely because SQL cannot support anything beyond its built-in datatypes.
If SQL supported a list type then it wouldn't be an issue at all.
A query formulation that deviates from prescribed operations of relations,
generally in a semi-relational language like SQL. Some use the term for
query expressions that violate relational principles like data independence
as well.
Oh yeah, and I am sure all these are all Gr
& Latin to you :-)Btw, Date's 1NF paper in the dbdebunk site is the simplest, yet the best one
I have seen so far on this topic.
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OOla6coLGHA.3732@.TK2MSFTNGP10.phx.gbl..
>SQL's concept of types/domains is a bit strange. All it provides is exposed
>representations using primitive built-in datatypes that are subject to
>different user interpretations.
>Due to insufficient type support in such systems, the type specificity and
>validity of such operations is often maintained and external to the DBMS;
>and often subject to different interpretations.
>With a resultset of concatenated values, it boils down to whether each
>column value in the resultset is treated as a scalar or not.
Sorry for the dely:(
Thanks for sharing your thoughts.(Where is your blog?)
I believe we are on the save wavelength here.I choose to present the
ideas in,shall we say,a slightly different style:)I regret that I
didn't use the word 'intrepretation' explicitly although I would point
out that I 'strongly' implied it everywhere including the title:)
An 'sql' db invites intrepretation and the inevitable arguments that too
often confuse the apples from the pears.Is this the result of sql
being the 'silent' type instead of the stong type?
But users have to work with what they have so lets keep the fruit coming:)
One would think that what is written about a product has importance.
Especially what is written by the vendor.It appears that there is
stuff coming from MS that is nonsense but becomes sense by merely
being repeated often enough.Funny and
:)But that is only my'intrepretation':)
You can do and get anything you want at Alice's resturant:)
Least I be accused of any ulterior motive picking on concatenating,the
next bit of fun will be:
'Is it ok to return a line number or a rank or a...'
If anyone liked this discussion you will love this one:)
>Btw, Date's 1NF paper in the dbdebunk site is the simplest, yet the best
one
I have seen so far on this topic.
Thanks and thanks.
No comments:
Post a Comment