concatenate - maybe?

I'm not sure what this process would be called.
I've got 2 columns...
[A] = FamilyName
[B] = FamilyMembers
I'm trying to make this...
[A] [B]
Doe Alan
Doe Bob
Doe Betty
Doe Joe
...into results like this...
[A] [B]
Doe Alan, Bob, Betty, Joe
i.e. Group column [B] into one field
thanksThis is called violating First Normal Form (1NF). It is a TOTAL
VIOLATION of the *most fundamental* principles of RDBMS.
Newbies without any business writing a database often post this request
in Newsgroups to show that they have never learned BASIC RDBMS
principles.
But even before SQL and RDBMS, the *most fundamental* principle of
*any* tiered architecture is that display is done in the client and
NEVER in the server. If you do not know this, then you should not be
programmng at all.|||Who the hell crapped in your soup?
Listen up a**hole!
There is NOT ONE LAW prohibiting me from joining the data into a query per
my preference!
And if this is the only way you can respond in a newsgroup, you need to move
on!
It is neither helpful, informative or appreciated.
Next time you run across a thread that pisses you off, move on!
You might ask yourself what would compel you to respond in such a manner!
No thanks!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130366077.636225.104700@.f14g2000cwb.googlegroups.com...
> This is called violating First Normal Form (1NF). It is a TOTAL
> VIOLATION of the *most fundamental* principles of RDBMS.
> Newbies without any business writing a database often post this request
> in Newsgroups to show that they have never learned BASIC RDBMS
> principles.
> But even before SQL and RDBMS, the *most fundamental* principle of
> *any* tiered architecture is that display is done in the client and
> NEVER in the server. If you do not know this, then you should not be
> programmng at all.
>|||Using the article http://www.aspfaq.com/show.asp?id=2529 as a basis the
solution below should help you out:
CREATE TABLE dbo.family
(
FamilyName VARCHAR(20),
FamilyMembers VARCHAR(20)
)
INSERT family SELECT 'Doe', 'Alan'
INSERT family SELECT 'Doe', 'Bob'
INSERT family SELECT 'Doe', 'Betty'
INSERT family SELECT 'Doe', 'Joe'
CREATE FUNCTION dbo.GetFamily
(
@.familyName VARCHAR(32)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.r VARCHAR(8000)
SELECT @.r = ISNULL(@.r+',', '') + familymembers
FROM dbo.family
WHERE familyname = @.familyName
RETURN @.r
END
GO
SELECT FamilyName, dbo.GetFamily(FamilyName)
FROM (SELECT familyname
FROM family
GROUP BY familyname) As a
- Peter Ward
WARDY IT Solutions
"shank" wrote:

> I'm not sure what this process would be called.
> I've got 2 columns...
> [A] = FamilyName
> [B] = FamilyMembers
> I'm trying to make this...
> [A] [B]
> Doe Alan
> Doe Bob
> Doe Betty
> Doe Joe
> ...into results like this...
> [A] [B]
> Doe Alan, Bob, Betty, Joe
> i.e. Group column [B] into one field
> thanks
>
>|||> But even before SQL and RDBMS, the *most fundamental* principle of
> *any* tiered architecture is that display is done in the client and
> NEVER in the server. If you do not know this, then you should not be
> programmng at all.
What absolute rubbish, you expose your lack of programming and real world
experience.
Formatting (what you term display) is done where it is most efficient to do
it, that may well be on the SELECT statement in the database or through
logic i.e. multiple row to single row conversion, again, within the
database.
Your ideas are 15 years old and way out of date.

> Newbies without any business writing a database often post this request
> in Newsgroups to show that they have never learned BASIC RDBMS
> principles.
You are not as respective nor experienced as you think you are.
You may also want to go back and fix this article where you have done a
fundemental mistake in not testing your design...
http://www.dbazine.com/ofinterest/oi-articles/celko14
From Kurt Sune's post of 26 Oct 2005, 14:21...
Tryed it in SQL server, doesnt work due to the fact that SQL server doesnt
treat count as sum.
This query gives the wrong answer, the usage of count taken from the
Celko-article.
SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
This one gives the right answer:
SELECT sum(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
,sum(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1130366077.636225.104700@.f14g2000cwb.googlegroups.com...
> This is called violating First Normal Form (1NF). It is a TOTAL
> VIOLATION of the *most fundamental* principles of RDBMS.
> Newbies without any business writing a database often post this request
> in Newsgroups to show that they have never learned BASIC RDBMS
> principles.
> But even before SQL and RDBMS, the *most fundamental* principle of
> *any* tiered architecture is that display is done in the client and
> NEVER in the server. If you do not know this, then you should not be
> programmng at all.
>|||Hi There,
Formatting (what you term display) is done where it is most efficient
to do
it, that may well be on the SELECT statement in the database or through
logic i.e. multiple row to single row conversion, again, within the
database.
Do you consider it (multiple row to single row conversion) fast
/Efficient ? Formatting should (sorry must ) be done on Client Side
.Send the ordered output to the client and process the rows in
single-level-break report manner.
We are all here to use the newsgroup not to abUSE it or flame someone.
Who the hell crapped in your soup?
Listen up a**hole!
There is NOT ONE LAW prohibiting me from joining the data into a query
per my preference!
The newsgroup is like a street shop where you pick those things which
suits you or you like ,ignore whatever you feel not good/worth.
With Warm regards
Jatinder Singh|||Jatinder,
Your answers seem to have got lost in my post.

> Do you consider it (multiple row to single row conversion) fast
> /Efficient ? Formatting should (sorry must ) be done on Client Side
> .Send the ordered output to the client and process the rows in
> single-level-break report manner.
The point i'm trying to get across is that you need do it where its most
efficient to do it, you just can't make a definitive statement that
formatting must be done in the client.
What if there where a few thousand rows being concatenated?
You need to think things through, there is usually a network in place
between the SQL Server and the client, you need to consider scalability what
ever solution you decide to use.
I prefer to do things on and in the SQL Server because its central, i won't
have that scalability problem and now with SQL Server 2005 having CLR
integration i can do a lot of stuff more efficiently.

> We are all here to use the newsgroup not to abUSE it or flame someone.
Completely agree, you are talking to the wrong person here.
If you check my posts you will see the only person i talk down to is Celko
because of his arrogant attitude and rudeness to people who use this
community. You will also note a lot of other people do the same.
Being rude and arrogant to grow name popularity just to sell a book is not
what these communities are for which is what really gets my goat.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1130396709.846350.275340@.g47g2000cwa.googlegroups.com...
> Hi There,
> Formatting (what you term display) is done where it is most efficient
> to do
> it, that may well be on the SELECT statement in the database or through
> logic i.e. multiple row to single row conversion, again, within the
> database.
>
> Do you consider it (multiple row to single row conversion) fast
> /Efficient ? Formatting should (sorry must ) be done on Client Side
> .Send the ordered output to the client and process the rows in
> single-level-break report manner.
> We are all here to use the newsgroup not to abUSE it or flame someone.
> Who the hell crapped in your soup?
> Listen up a**hole!
> There is NOT ONE LAW prohibiting me from joining the data into a query
> per my preference!
> The newsgroup is like a street shop where you pick those things which
> suits you or you like ,ignore whatever you feel not good/worth.
>
> With Warm regards
> Jatinder Singh
>|||Hi Tony,
Thanks for your input , but using scalar function to produce the
concatenated output even for 100 or 1000 rows is not advisable ( I used
the term advisable ; because basic rules help us to write good and
managable code [sorry queries] )
If someone wish to use scalar function there is no one stopping him
or her , but in case the length of concated string crosses 8000 ,Isnot
it the output is incorrect?
SQL Server give us Inline and Scalar function but I really donot
use any of them . What I feel is scalar function can be replaced by
formula directly and an INline function can be replaced by a view with
proper WHERE Clause .So ,where does a function really fit in?
With Warm regards
Jatinder Singh|||It depends what you are doing, if you want concatenated output then yes you
need to consider the 8000 byte limit for a scalar function, alternatively
use a table variable.
This all becomes significantly better and more performant in SQL Server 2005
with CLR, also the TSQL functions are faster as well.
Say you are concatenating 20 values, having 20 joins starts to become
unmanageable and difficult to maintain.
You might be concatenating them for good reason, for instance a message
board thread just like this one. There are tons of other reasons too.
I think i'll finish with a call back to my original post - you cannot
definitively state not to do stuff in SQL Server, it depends what you are
doing - you must test each method and make sure it scales!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1130416953.071865.8500@.f14g2000cwb.googlegroups.com...
> Hi Tony,
> Thanks for your input , but using scalar function to produce the
> concatenated output even for 100 or 1000 rows is not advisable ( I used
> the term advisable ; because basic rules help us to write good and
> managable code [sorry queries] )
> If someone wish to use scalar function there is no one stopping him
> or her , but in case the length of concated string crosses 8000 ,Isnot
> it the output is incorrect?
> SQL Server give us Inline and Scalar function but I really donot
> use any of them . What I feel is scalar function can be replaced by
> formula directly and an INline function can be replaced by a view with
> proper WHERE Clause .So ,where does a function really fit in?
> With Warm regards
> Jatinder Singh
>|||I am appalled at the arrogance of this. I have been designing and building
databases since 1978 and Hierarchical and Codasyl, I then moved on to Relati
onal and NO relational fits all the rules of Codd.
What a piece of arrant nonsense. What this 'CELKO' person does not realise
is that RDBMS is itself a cludge.
I noticed a complete absence of creative input from 'CELKO'.
What this person is doing here is stifling innovation. As other posts sai
d, what if there are thousands of rows.
The ART of database design is in initially normalizing, and THEN de-normaliz
ing for efficiency. Retaining NF for the sake of NF is mentally pathetic.
I have successfully built many very large databases, and many small ones, I
have designed my own DBMS, and worked closely with the architects of some o
f the world's most important DBMSs, you sir, are talking out of your hat.
As for the original post, good question, but I too think you will be stymied
by the 8k limit.
Jerry

quote:

---

Originally posted by --CELKO--
This is called violating First Normal Form (1NF). It is a TOTAL
VIOLATION of the *most fundamental* principles of RDBMS.
Newbies without any business writing a database often post this request
in Newsgroups to show that they have never learned BASIC RDBMS
principles.
But even before SQL and RDBMS, the *most fundamental* principle of
*any* tiered architecture is that display is done in the client and
NEVER in the server. If you do not know this, then you should not be
programmng at all.

---