Well, well, well!
Oh my! Phew! It's Anthony Molinaro! Hehe. How have you been? Ahhhhhh, yes! The book "SQL Cookbook" by Anthony Molinaro! It's an outstanding one and I use it all of the time! It's a must have book!

Have a great Day!






Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Jeanine Blomberg is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below. Thank you in advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----Original Message-----
From: Anthony Molinaro [mailto:mysqldba-ezmlmshield-x893300.[Email address protected]
Sent: Monday, October 29, 2007 11:52 AM
To: LazyDBA Discussion
Subject: RE: Multiple Rows



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 [mailto:mysqldba-ezmlmshield-x76351952.[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: 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

MySQL LazyDBA home page