Nice one Anthony!
I am sure Spencer will appreciate your input.

... and so do I! this was not as simple as it initially sounded.

Amazon here I come :-)


At 2007/10/29 05:52 PM, Anthony Molinaro wrote:
>**A LazyDBA.com subscriber has responded to your lazydba.com post**
>**LazyDBA.com mail shield has forwarded you this email,
>**and removed any attachments, and kept your email address secret
>**from this person, and any viruses/trojans.
>**If you reply to this email, the person will see your email address as
>normal
>**Anything below this line is the original email text
>
>
>
>Spike,
>
>The point of the book is to provide answers for common problems
>such as this one so time isn't spent asking the same questions that have
>been asked a million times before. :)
>
>Nevertheless, to show I'm not some pr1ck just trying to advertise my book...
>
>using the following table:
>
>select deptno, ename from emp order by 1,2
>
> DEPTNO ENAME
>---------- ----------
> 10 CLARK
> 10 KING
> 10 MILLER
> 20 ADAMS
> 20 FORD
> 20 JONES
> 20 SCOTT
> 20 SMITH
> 30 ALLEN
> 30 BLAKE
> 30 JAMES
> 30 MARTIN
> 30 TURNER
> 30 WARD
>
>The goal is the following result set:
>
>DEPTNO EMPS
>------ ----------------------------------------
> 10 CLARK,KING,MILLER
> 20 ADAMS,FORD,JONES,SCOTT,SMITH
> 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
>
>
>
>MYSQL:
>
>select deptno,
>group_concat(ename order by ename separator ',') as emps
>from emp
>group by deptno
>
>
>
>Oracle:
>
>select deptno,
> ltrim(
> max(sys_connect_by_path(ename,','))
> keep(dense_rank last order by level),',') emps
> from (
>select deptno,ename,
> row_number()over(partition by deptno order by ename) rn
> from emp
> ) tmp
> start with rn=1
> connect by prior rn=rn-1 and deptno=prior deptno
> group by deptno
>
>
>
>Sql server 2005:
>
> with tmp (deptno, cnt, list, empno, len)
> as (
> select deptno, count(*) over (partition by deptno),
> cast(ename as varchar(100)),
> empno,
> 1
> from emp
> union all
> select tmp.deptno, tmp.cnt,
> cast(tmp.list + ',' + e.ename as varchar(100)),
> e.empno, tmp.len+1
> from emp e, tmp
> where e.deptno = tmp.deptno
> and e.empno > tmp.empno
> )
> select deptno,list
> from tmp
> where len = cnt
> order by 1
>
>
>As you can see, MYSQL, using group_concat, is by far the cleanest approach.
>
>Regards,
> ant
>
>-----Original Message-----
>From: Spike [Email address protected]
>Sent: Monday, October 29, 2007 10:29 AM
>To: LazyDBA Discussion
>Subject: Re: Multiple Rows
>
>We were hoping for an example or a suggestion ...not a book advert.
>
>Anthony Molinaro wrote:
> > I show how to do it in oracle, mysql, db2, postgresql and sql server in
> my book:
> >
> > http://www.amazon.com/Cookbook-Cookbooks-OReilly-Anthony-Molinaro/dp/0
> > 596009763/ref=pd_bbs_sr_2/103-2289700-5418224?ie=UTF8&s=books&qid=1193
> > 668116&sr=1-2
> >
> > There are many ways to do it, I highlight a few in my book (I also
> > show an alternative for Oralce online using the MODEL clause in 10g On
> the errata page of my book on the oreilly site).
> >
> > Of all the db's though, I'd say that mysql's group_concat is the best
> builtin for this sort of problem.
> >
> > Regards,
> > Anthony
> >
> > -----Original Message-----
> > From: Αναστάσιος Î
> απαδόπουλος
> > [mailto:mysqldba-ezmlmshield-x19036192.[Email address protected]
> > Sent: Monday, October 29, 2007 5:13 AM
> > To: LazyDBA Discussion
> > Subject: RE: Multiple Rows
> >
> > Then he posted to the wrong list :P
> >
> > It would be interesting though if anyone knows how to do this on Oracle
> or MSSQL...
> >
> > Best regards,
> >
> > Anastasios Papadopoulos
> >
> > Αναστάσιος Î
> απαδόπουλος Software Solutions
> > Architect SalesManager Hellas Customer Relationship Management
> > Solutions
> > Τηλ.: (+30) 210 6083485
> > Φαξ: (+30) 210 6018453
> > e-mail: [Email address protected]
> > www.salesmanager.gr
> >
> >
> > -----Original Message-----
> > From: Rahul Chaudhari [mailto:mysqldba-ezmlmshield-x38539937.[Email
> > address protected]
> > Sent: Monday, October 29, 2007 11:56 AM
> > To: LazyDBA Discussion
> > Subject: Re: Multiple Rows
> >
> > I think he wants in oracle an not in mysql database.
> >
> > Regards,
> > Rahul Chaudhari
> > ----- Original Message -----
> > From:
> "Αναστάσιος
> ΞÂ
> Ξ±Ο€Ξ±Ξ΄ΟŒΟ€ΞΏΟ…Ξ»ΞΏΟ‚
> "
> > <mysqldba-ezmlmshield-x19150961.[Email address protected]
> > To: "LazyDBA Discussion" <[Email address protected]
> > Sent: Monday, October 29, 2007 1:12 PM
> > Subject: RE: Multiple Rows
> >
> >
> >
> >> You should try the group_concat function in MySQL, a great
> >> feature missing in most RDBMS (AFAIK).
> >>
> >> HTH,
> >>
> >> Anastasios Papadopoulos
> >>
> >>
> ÁíΓʽóôÜóéïò
>
> >>
> ÐΓʽΓ°ΓʽΓüðïáëïò
> >> Software Solutions Architect
> >> SalesManager Hellas
> >> Customer Relationship Management Solutions
> >> Ôçë.: (+30) 210 6083485
> >> Γ–ΓʽΓ: (+30) 210 6018453
> >> e-mail: [Email address protected]
> >> www.salesmanager.gr
> >>
> >> -----Original Message-----
> >> From: Spencer Gregory A IT2 (LAS CC)
> >> [mailto:mysqldba-ezmlmshield-x30149568.[Email address protected]
> >> Sent: Sunday, October 28, 2007 9:45 PM
> >> To: LazyDBA Discussion
> >> Subject: Multiple Rows
> >>
> >> I have an assignment to create a Movies database with the movie
> >> listed along with the actors. My problem is I need to display each
> >> movie one time along with all the actors;
> >>
> >>
> >>
> >> Cold Mountain- Jude Law, Nicole Kidman
> >>
> >>
> >>
> >> Enemy at the Gates- Jude Law, Rachel Weisz
> >>
> >>
> >>
> >> But what I get is like this;
> >>
> >>
> >>
> >> Cold Mountain- Jude Law
> >>
> >> Cold Mountain- Nicole Kidman
> >>
> >> Enemy at the Gates- Jude Law
> >>
> >> Enemy at the Gates- Rachel Weisz
> >>
> >>
> >>
> >> How do I avoid displaying duplicating the movie title?
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> Very respectfully,
> >>
> >> IT2 Spencer
> >>
> >> CCNA
> >>
> >> [Email address protected]
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> ---------------------------------------------------------------------
> >> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> >> unsubscribe, e-mail: mysqldba-[Email address protected] Website :
> >> http://www.LazyDBA.com
> >>
> >>
> >>
> >> ---------------------------------------------------------------------
> >> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> >> unsubscribe, e-mail: mysqldba-[Email address protected] Website :
> >> http://www.LazyDBA.com
> >>
> >>
> >>
> >
> >
> >
> >
> > ---------------------------------------------------------------------
> > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> > unsubscribe, e-mail: mysqldba-[Email address protected] Website :
> > http://www.LazyDBA.com
> >
> >
> >
> > ---------------------------------------------------------------------
> > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> > unsubscribe, e-mail: mysqldba-[Email address protected] Website :
> > http://www.LazyDBA.com
> >
> >
> >
> > ---------------------------------------------------------------------
> > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
> > unsubscribe, e-mail: mysqldba-[Email address protected] Website :
> > http://www.LazyDBA.com
> >
> >
> >
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
>unsubscribe, e-mail: [Email address protected] Website : http://www.LazyDBA.com


MySQL LazyDBA home page