SQL行转列

CREATE TABLE TABLE_A (
NAME VARCHAR2(20),
SUBJECT VARCHAR2(20),
SCORE int(10)
);
SQL2000以上版本和mysql
select name,
sum (case subject when ‘语文’ then score else 0 end) as chinese,
sum (case subject when ‘数学’ then score else 0 end) as math,
sum (case subject when ‘英语’ then score else 0 end) as english
from Table_A
group by name;
case 条件列 when ‘条件(列的值)’ then 要取的值的列 else 0(把null替换为0) end
或者 case when 条件列=‘条件(列的值)’ then 要取的值的列 else 0(把null替换为0) end

SQL2005以上版本
select * from Table_A pivot (max(score)for subject in(‘语文’,‘数学’,‘英语’))
select * from 表名 pivot (max(取值的列)for 条件列 in(条件列的值，多个逗号隔开))

列转行

–方法一：使用union all mysql适用
select NAME,‘语文’ as SUBject,chinese as score from Table_B union all
select NAME,‘数学’ as SUBject,math as score from Table_B union all
select NAME,‘英语’ as SUBject,english as score from Table_B
order by name,subject desc
–方法二：使用unpivot
select name,subject,score from Table_B
unpivot
(score for subject in (chinese,math,english))