----- Original Message -----
From: "2wsxdr5" <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]
