MySQL ODBC Connector - returning empty columns

Author

returning empty columns

2wsxdr5

2006-02-28, 8:28 pm

This is probably going to sound like an odd request, but is there a way
to return empty columns in Mysql. For example a roll call sheet I want
to do a select of names from my table and then add a column for each of
the next 12 weeks. I tried this.....

SELECT `Call`, concat(FName, ' ', LName) as Name, 'Mar-6' ,'Mar-13',
'Mar-20', 'Mar-27'
FROM table
Order BY LName, FName

The problem is it put that date on every row and I just want the names
to show up in the column header I know I could just write some php code
to print out a table with the columns but I have a handy php function
the prints the out put of a query in a table already so if I can find
the right query I don't have to change that code any.

--
Chris W
KE5GIX

Gift Giving Made Easy
Get the gifts you want &
give the gifts they want
One stop wish list for any gift,
from anywhere, for any occasion!
http://thewishzone.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Rhino

2006-02-28, 8:28 pm

----- Original Message -----
From: "2wsxdr5" <2wsxdr5@cox.net>
To: <mysql@lists.mysql.com>
Sent: Tuesday, February 28, 2006 3:43 AM
Subject: returning empty columns

> This is probably going to sound like an odd request, but is there a way to
> return empty columns in Mysql. For example a roll call sheet I want to do
> a select of names from my table and then add a column for each of the
> next 12 weeks. I tried this.....
>
> SELECT `Call`, concat(FName, ' ', LName) as Name, 'Mar-6' ,'Mar-13',
> 'Mar-20', 'Mar-27'
> FROM table
> Order BY LName, FName
>
> The problem is it put that date on every row and I just want the names to
> show up in the column header I know I could just write some php code to
> print out a table with the columns but I have a handy php function the
> prints the out put of a query in a table already so if I can find the
> right query I don't have to change that code any.
>
In 20+ years of writing and teaching SQL I can't remember anyone ever
wanting to do this but you can easily get a blank column (or twelve) with
just a slight modification of the technique you already tried. The values
you put within apostrophes, like 'Mar-20', are just literals so, instead of
putting text between the apostrophes, just write two consecutive
apostrophes. Therefore:

SELECT `Call`, concat(FName, ' ', LName) as Name, '' ,'', '', ''
FROM table
Order BY LName, FName

will give you the same information you got before but each of the four extra
columns should be empty. If you want those columns to have titles, use an AS
expression, like this:

SELECT `Call`, concat(FName, ' ', LName) as Name, '' as "Eenie" ,'' as
"Meenie", '' as "Miney", '' as "Moe"
FROM table
Order BY LName, FName

Be careful when typing my examples: to get a blank column, you need two
consecutive apostrophes (sometimes called single quotes) but the AS
expressions need to be within double quotes.

Wait! I was wrong! I just tried it using single quotes in the AS expressions
and it still worked fine:

SELECT `Call`, concat(FName, ' ', LName) as Name, '' as 'Eenie' ,'' as
'Meenie', '' as 'Miney', '' as 'Moe'
FROM table
Order BY LName, FName

It even worked when I used backtics (`):

SELECT `Call`, concat(FName, ' ', LName) as Name, '' as `Eenie` ,'' as
`Meenie`, '' as `Miney`, '' as `Moe`
FROM table
Order BY LName, FName

You can also use pairs of double quotes to create the empty columns:

SELECT `Call`, concat(FName, ' ', LName) as Name, "" as `Eenie` ,"" as
`Meenie`, "" as `Miney`, "" as `Moe`
FROM table
Order BY LName, FName

But you can't use pairs of backtics:

SELECT `Call`, concat(FName, ' ', LName) as Name, `` as `Eenie` , `` as
`Meenie`, `` as `Miney`, `` as `Moe`
FROM table
Order BY LName, FName

So, MySQL is more tolerant than I realized.

--
Rhino

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/271 - Release Date: 28/02/2006

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Price, Randall

2006-02-28, 8:28 pm

Chris,

See if this works for you:

SELECT
' ' as 'Call'
, concat(FName, ' ', LName) as Name
, ' ' as 'Mar-6'
, ' ' as 'Mar-13'
, ' ' as 'Mar-20'
, ' ' as 'Mar-27'
FROM
table
ORDER BY
LName, FName

Notice that there is a single blank space being returned for the Call,
Mar-6, Mar-13, Mar-20, Mar-27 columns. I tried returning null as well
as an empty string (i.e., '') but those did NOT give me column headings.

Hope this helps.

Randall Price
VT.SETI.IAD.MIG:Microsoft Implementation Group
http://vtmig.vt.edu
Randall.Price@vt.edu

-----Original Message-----
From: 2wsxdr5 & #91;mailto:2wsxdr5@c
ox.net]=20
Sent: Tuesday, February 28, 2006 3:43 AM
To: mysql@lists.mysql.com
Subject: returning empty columns

This is probably going to sound like an odd request, but is there a way=20
to return empty columns in Mysql. For example a roll call sheet I want=20
to do a select of names from my table and then add a column for each of

the next 12 weeks. I tried this.....

SELECT `Call`, concat(FName, ' ', LName) as Name, 'Mar-6' ,'Mar-13',=20
'Mar-20', 'Mar-27'
FROM table
Order BY LName, FName

The problem is it put that date on every row and I just want the names=20
to show up in the column header I know I could just write some php code

to print out a table with the columns but I have a handy php function=20
the prints the out put of a query in a table already so if I can find=20
the right query I don't have to change that code any.

--=20
Chris W
KE5GIX

Gift Giving Made Easy
Get the gifts you want &=20
give the gifts they want
One stop wish list for any gift,=20
from anywhere, for any occasion!
http://thewishzone.com

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=...ll.Price@vt.edu

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links