mysql 行轉換為列

標籤：blog   http   io   ar   使用   sp   for   on   資料   

http://www.cnblogs.com/acelove/archive/2004/11/29/70434.html上提到了一個行列轉換的方法,其實在2005裡有更可讀的寫法,就是使用pivot運算子:

create table abc 
(
student varchar(50),
class varchar(50),
grade int
)
INSERT INTO abc
SELECT ‘孫小美‘,‘數學‘,10 UNION ALL
SELECT ‘孫小美‘,‘語文‘,20 UNION ALL
SELECT ‘孫小美‘,‘英語‘,30 UNION ALL
SELECT ‘阿土伯‘,‘數學‘,40 UNION ALL
SELECT ‘阿土伯‘,‘語文‘,50 UNION ALL
SELECT ‘阿土伯‘,‘英語‘,60 UNION ALL
SELECT ‘小叮鐺‘,‘數學‘,70 UNION ALL
SELECT ‘小叮鐺‘,‘語文‘,80 UNION ALL
SELECT ‘小叮鐺‘,‘英語‘,90

SELECT 
    student,
    MAX(數學) AS 數學,
    MAX(語文) AS 語文,
    MAX(英語) AS 英語
FROM
    (
    SELECT 
        student,
        CASE class WHEN ‘數學‘ THEN grade END AS 數學,
        CASE class WHEN ‘語文‘ THEN grade END AS 語文,
        CASE class WHEN ‘英語‘ THEN grade END AS 英語
    FROM abc
    ) AS a
GROUP BY student 
--用pivot運算子
select student,[數學] as ‘數學‘,[語文] as ‘語文‘ ,[英語] as ‘英語‘
from
 (select * from abc) as source
    pivot
    (
        sum(grade)
        for class  in
        ([數學],[語文],[英語])
    ) as p

不過對於不知道具體列名的程式還真不好解決,產生動態sql話(調用sp_executesql),臂如:

declare @sql varchar(8000)
set @sql = ‘select student,‘
select @sql = @sql + ‘sum(case class when ‘‘‘+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)

倒是可以,不過卻與當前預存程序調用不屬於一批命令了,定義的CTE,局部暫存資料表也訪問不了,似乎只有放到業務層或資料層去解決了.

mysql 行轉換為列