MySQL rows converted to columns

Http://www.cnblogs.com/acelove/archive/2004/11/29/70434.html mentions a method of row and column conversion, in fact, in 2005 there is a more readable notation, that is, using the pivot operator:

CREATE TABLE ABC
(
Student varchar (50),
Class varchar (50),
Grade int
)
INSERT into ABC
SELECT ' Sun Xiaomi ', ' math ', UNION all
SELECT ' Sun Xiaomi ', ' language ', UNION all
SELECT ' Sun Xiaomi ', ' English ', UNION all
SELECT ' Artuber ', ' math ', all
SELECT ' Artuber ', ' language ', UNION all
SELECT ' Artuber ', ' English ', UNION all
SELECT ' small ding ', ' math ', ' UNION all '
SELECT ' small ding ', ' Chinese ', UNION all
SELECT ' Little ding clang ', ' English ', 90

SELECT
Student
MAX (mathematics) as mathematics,
MAX (language) as language,
MAX (English) as English
From
(
SELECT
Student
Case class when ' math ' then grade END as math,
Case class when ' language ' then grade END as language,
Case class when ' English ' then grade END as English
From ABC
) as a
GROUP by Student
--Using the pivot operator
Select student,[Math] as ' math ', [language] as ' language ', [English] as ' English '
From
(SELECT * FROM ABC) as source
Pivot
(
SUM (grade)
For class in
([Mathematics],[Chinese],[English])
) as P

However, for a program that does not know the specific column name is not very good solution, generate dynamic SQL session (call sp_executesql), arm such as:

DECLARE @sql varchar (8000)
Set @sql = ' Select student, '
Select @sql = @sql + ' sum (case class "+class+" then grade else 0 end) as ' +class+ ', '
From (SELECT DISTINCT class from ABC) as a
Select @sql = Left (@sql, Len (@sql)-1) + ' from ABC GROUP by Student '
EXEC (@sql)

Yes, but with the current stored procedure call does not belong to a batch of commands, the definition of the CTE, the local temporary table can not access, it seems only to be placed in the business layer or the data layer to solve.

MySQL rows converted to columns