SQL Question

------=_NextPart_000_0003_01C59CCC.49B928E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Is there a way in MySQL to display a column in a horizontal fashion.... =
We're using version 4.1.11. Below is an example of what I'm trying to =
do.

select firstlast, fte from requests join fte on ....

firstlast fte
------------------------------------------------------------ ---------
Smith, Bill Jones, Bob Stone, Pat 2.0

The names in the firstlast column will display on one row not in a =
column.

Thanks,


Melissa
------=_NextPart_000_0003_01C59CCC.49B928E0--

Re: SQL Question

------=_NextPart_000_000D_01C59CD6.B604A7E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

RE: SQL QuestionI've looked in the MySQL documentation.... and have not =
found anything. I'm attaching the whole query to see if that might =
help.

select owr.requestid, fte.function,fte.organization,fte.company,
(select distinct fte.firstlast from workforceplan_workers ww =
where ww.requestid =3D fte.requestid) AS `FirstLastName`,
fte.2006,fte.2007,fte.2008,fte.2009,fte.2010,fte.2011,fte.20 12
from workforceplan_request owr join
(select distinct r.requestid, =
function,organization,company,firstlast,
IFNULL((select distinct fte from workforceplan_fte sf join =
workforceplan_request sr
on sf.requestid =3D sr.requestid and =
sf.requestid =3D r.requestid
join workforceplan_workers sw on =
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2006'),'0') AS `2006`,
IFNULL((select distinct fte from workforceplan_fte sf join =
workforceplan_request sr
on sf.requestid =3D sr.requestid and =
sf.requestid =3D r.requestid
join workforceplan_workers sw on =
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2007'),'0') AS `2007`,
IFNULL((select distinct fte from workforceplan_fte sf join =
workforceplan_request sr
on sf.requestid =3D sr.requestid and =
sf.requestid =3D r.requestid
join workforceplan_workers sw on =
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2008'),'0') AS `2008`,
IFNULL((select distinct fte from workforceplan_fte sf join =
workforceplan_request sr
on sf.requestid =3D sr.requestid and =
sf.requestid =3D r.requestid
join workforceplan_workers sw on =
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2009'),'0') AS `2009`,
IFNULL((select distinct fte from workforceplan_fte sf join =
workforceplan_request sr
on sf.requestid =3D sr.requestid and =
sf.requestid =3D r.requestid
join workforceplan_workers sw on =
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2010'),'0') AS `2010`,
IFNULL((select distinct fte from workforceplan_fte sf join =
workforceplan_request sr
on sf.requestid =3D sr.requestid and =
sf.requestid =3D r.requestid
join workforceplan_workers sw on =
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2011'),'0') AS `2011`,
IFNULL((select distinct fte from workforceplan_fte sf join =
workforceplan_request sr
on sf.requestid =3D sr.requestid and =
sf.requestid =3D r.requestid
join workforceplan_workers sw on =
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2012'),'0') AS `2012`
from workforceplan_request r join workforceplan_workers w
on w.requestid =3D r.requestid join workforceplan_fte f on =
r.requestid =3D f.requestid) fte on owr.requestid =3D fte.requestid
order by 1


----- Original Message -----=20
From: Don Lancaster=20
To: 'Melissa Dougherty'=20
Sent: Tuesday, August 09, 2005 10:38 AM
Subject: RE: SQL Question


Good question! I don't know of any way offhand to do this. I think =
if I had to do something like this, I'd send the output to a textfile, =
import it into Excel or another spreadsheet, and transpose the data. If =
I had the MySQL manual in front of me, I might be able to find something =
for you, but alas, I don't... =20

-----Original Message-----=20
From: Melissa Dougherty [mailto:melissa@cse-corp.com]=20
Sent: Tuesday, August 09, 2005 10:23 AM=20
To: win32@lists.mysql.com=20
Subject: SQL Question=20



Is there a way in MySQL to display a column in a horizontal =
fashion.... We're using version 4.1.11. Below is an example of what I'm =
trying to do.

select firstlast, fte from requests join fte on ....=20

firstlast fte=20
------------------------------------------------------------ ---------=20
Smith, Bill Jones, Bob Stone, Pat 2.0=20

The names in the firstlast column will display on one row not in a =
column.=20

Thanks,=20



Melissa=20

------=_NextPart_000_000D_01C59CD6.B604A7E0--

RE: SQL Question

What about...


select group_concat(firstlast), fte=20
from requests join fte on ....=20
group by fte


Where the "fte" would be made up of all the columns the "firstlast"
grouped across.
The group_concat has distinct, order and separator features if needed. I
used your
first example for obvious reasons.

Ed=20

-----Original Message-----
From: Melissa Dougherty [mailto:melissa@cse-corp.com]=20
Sent: Tuesday, August 09, 2005 9:37 AM
To: Don Lancaster
Cc: win32@lists.mysql.com
Subject: Re: SQL Question

RE: SQL QuestionI've looked in the MySQL documentation.... and have not
found anything. I'm attaching the whole query to see if that might
help.

select owr.requestid, fte.function,fte.organization,fte.company,
(select distinct fte.firstlast from workforceplan_workers ww
where ww.requestid =3D fte.requestid) AS `FirstLastName`,
fte.2006,fte.2007,fte.2008,fte.2009,fte.2010,fte.2011,fte.20 12
from workforceplan_request owr join
(select distinct r.requestid,
function,organization,company,firstlast,
IFNULL((select distinct fte from workforceplan_fte sf join
workforceplan_request sr
on sf.requestid =3D sr.requestid and
sf.requestid =3D r.requestid
join workforceplan_workers sw on
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2006'),'0') AS `2006`,
IFNULL((select distinct fte from workforceplan_fte sf join
workforceplan_request sr
on sf.requestid =3D sr.requestid and
sf.requestid =3D r.requestid
join workforceplan_workers sw on
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2007'),'0') AS `2007`,
IFNULL((select distinct fte from workforceplan_fte sf join
workforceplan_request sr
on sf.requestid =3D sr.requestid and
sf.requestid =3D r.requestid
join workforceplan_workers sw on
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2008'),'0') AS `2008`,
IFNULL((select distinct fte from workforceplan_fte sf join
workforceplan_request sr
on sf.requestid =3D sr.requestid and
sf.requestid =3D r.requestid
join workforceplan_workers sw on
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2009'),'0') AS `2009`,
IFNULL((select distinct fte from workforceplan_fte sf join
workforceplan_request sr
on sf.requestid =3D sr.requestid and
sf.requestid =3D r.requestid
join workforceplan_workers sw on
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2010'),'0') AS `2010`,
IFNULL((select distinct fte from workforceplan_fte sf join
workforceplan_request sr
on sf.requestid =3D sr.requestid and
sf.requestid =3D r.requestid
join workforceplan_workers sw on
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2011'),'0') AS `2011`,
IFNULL((select distinct fte from workforceplan_fte sf join
workforceplan_request sr
on sf.requestid =3D sr.requestid and
sf.requestid =3D r.requestid
join workforceplan_workers sw on
sw.requestid =3D sr.requestid and sr.requestid =3D r.requestid
where year =3D '2012'),'0') AS `2012`
from workforceplan_request r join workforceplan_workers w
on w.requestid =3D r.requestid join workforceplan_fte f on =
r.requestid
=3D f.requestid) fte on owr.requestid =3D fte.requestid
order by 1


----- Original Message -----=20
From: Don Lancaster=20
To: 'Melissa Dougherty'=20
Sent: Tuesday, August 09, 2005 10:38 AM
Subject: RE: SQL Question


Good question! I don't know of any way offhand to do this. I think
if I had to do something like this, I'd send the output to a textfile,
import it into Excel or another spreadsheet, and transpose the data. If
I had the MySQL manual in front of me, I might be able to find something
for you, but alas, I don't... =20

-----Original Message-----=20
From: Melissa Dougherty [mailto:melissa@cse-corp.com]=20
Sent: Tuesday, August 09, 2005 10:23 AM=20
To: win32@lists.mysql.com=20
Subject: SQL Question=20



Is there a way in MySQL to display a column in a horizontal
fashion.... We're using version 4.1.11. Below is an example of what I'm
trying to do.

select firstlast, fte from requests join fte on ....=20

firstlast fte=20
------------------------------------------------------------ ---------=20
Smith, Bill Jones, Bob Stone, Pat 2.0=20

The names in the firstlast column will display on one row not in a
column.=20

Thanks,=20



Melissa=20

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

RE: SQL Question

I think what you really need is some sort of cross tabulation. Have a
look at this link. I think it will give you some ideas.

http://mysql.mirror.nedlinux.nl/tech-resources/articles/wiza rd/index.htm
l

John Bonnett
Computer Scientist
Carl Zeiss Vision GmbH
Lonsdale, South Australia
Phone: +61 8 8392 8366
Fax: +61 8 8392 8400


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org