Renaming Columns in an Sql Select Statement

Question

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.

Accepted Answer

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'

Answer 2

you have to rename each column

SELECT col1 as MyCol1,
       col2 as MyCol2,
 .......
 FROM `foobar`

Answer 3

select column1 as xyz,
      column2 as pqr,
.....

from TableName;

Renaming Columns in an Sql Select Statement

3 Answers

Must Read

Your Answer

Robert Thorne

Renaming Columns in an Sql Select Statement

3 Answers

Must Read

Your Answer

Sign up or log in

Post as a guest

Robert Thorne