----- Original Message -----
From: "2wsxdr5" <2wsxdr5@stripped>
To: <mysql@stripped>
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
