CODEWITHSUNDEEP
mysqlsql
Andrew Cheong
Andrew Cheong

Reputation: 30273

Renaming Columns in an SQL SELECT Statement

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.

Upvotes: 32

Views: 185073

Answers (3)

Shikha Singh
Shikha Singh

Reputation: 21

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

from TableName;

Upvotes: 2

StuartLC
StuartLC

Reputation: 107237

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'

Upvotes: 49

Joe G Joseph
Joe G Joseph

Reputation: 24046

you have to rename each column

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

Upvotes: 11

Related Questions