I would like to rename the columns in the results of a SELECT expression. Of course, the following doesn't work:
SELECT * AS foobar_* FROM `foobar`
As I'm new to SQL, I think I'm just missing a concept, tool, or keyword that would lead to the answer. A hint in the right direction would be appreciated. Thanks!
UPDATE
I'm looking for a generic way to do this, and MySQL-specific techniques are absolutely fine.
In short, I'm writing a tool that "exports" the results of MySQL queries to Google Spreadsheets (via the Google Data API). Some queries are joins, so to make columns unique I wanted to prefix all column names with their respective table names.
3 Answers
You can alias the column names one by one, like so
SELECT col1 as `MyNameForCol1`, col2 as `MyNameForCol2`
FROM `foobar`
Edit You can access INFORMATION_SCHEMA.COLUMNS directly to mangle a new alias like so. However, how you fit this into a query is beyond my MySql skills :(
select CONCAT('Foobar_', COLUMN_NAME)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'Foobar'
you have to rename each column
SELECT col1 as MyCol1,
col2 as MyCol2,
.......
FROM `foobar`
select column1 as xyz,
column2 as pqr,
.....
from TableName;
