MySQL pivoting and assigning custom column names

Question

I'm performing a query in MySQL like so...

select employee_id, sale_date, count(id) 
from sales 
group by employee_id, extract(year_month from sale_date);

The data comes back like...

1 1/1/2014 5
1 2/1/2014 6
1 3/1/2014 4
2 1/1/2014 4
2 2/1/2014 10
2 3/1/2014 0
3 1/1/2014 0
3 2/1/2014 0
3 3/1/2014 8

I pivot this table and what I want to do is display in the following format

ID    Jan    Feb    Mar
1     5      6      4
2     4      10     0
3     0      0      8

In the this table, what I want is column names that correspond to the month. If you're in the middle of the year though, the I want the 1st column to be whatever month you're in, not Jan.

As to my question - is it possible anyhow to set the name of the column dynamically within the same SQL statement? Since I have it pivoted, I can extract the month name, I'm just missing a step of actually writing the column name.

Answer 1

No. The column name or column alias cannot be modified dynamically within the execution of a SQL SELECT statement.

So a query that produces the result shown in the question, with those column names, we would need to execute a SQL statement that looks something like this:

SELECT id    AS `ID`
     , expr  AS `Jan`
     , expr  AS `Feb`
  FROM ...

This requirement is because of how SQL operates. Boils down to a pretty basic rule: the identifiers (e.g. table names, column names, column aliases) must be specified in the SQL text.

It would be possible to dynamically create a statement like the one above, as a preceding step. And it's possible to use another, separate SQL statement to help you do that. The goal of the pre-steps would be to generate a statement like the one above, to get a column alias assigned. As far as how to do that, that question has been asked multiple times on StackOverflow.

While it is possible to get a SQL statement generated dynamically, Frequently, the pivoting and aliasing can be avoided in the SQL, and handled in the client instead.