可以使用case when或者decode实现。select max(case col1 when 'A' then col2 end) as A, max(case col1 when 'B' then col2 end) as B, max(case col1 when 'C' then col2 end) as C from t;
行转列:用decode很方便,很强大,下面给你举个简单例子: 行转列例子: select * from grade select student ,max(decode(subject,'en',grade,null)) 英语 ,max(decode(subject,'ch',grade,null)) 语文, max(decode(subject,'ma',grade,null)) 数学 from grade group by student 列转行:创建测试表 create table test as select student ,max(decode(subject,'en',grade,null)) 英语 ,max(decode(subject,'ch',grade,null)) 语文, max(decode(subject,'ma',grade,null)) 数学 from grade group by studentselect * from testselect student,'英语' 科目,英语 from test union all select student,'语文' 科目,语文 from test union all select student,'数学' 科目,数学 from test
max(case col1 when 'B' then col2 end) as B,
max(case col1 when 'C' then col2 end) as C
from t;
行转列例子:
select * from grade select student ,max(decode(subject,'en',grade,null)) 英语 ,max(decode(subject,'ch',grade,null)) 语文,
max(decode(subject,'ma',grade,null)) 数学 from grade group by student 列转行:创建测试表
create table test as
select student ,max(decode(subject,'en',grade,null)) 英语 ,max(decode(subject,'ch',grade,null)) 语文,
max(decode(subject,'ma',grade,null)) 数学 from grade group by studentselect * from testselect student,'英语' 科目,英语 from test
union all
select student,'语文' 科目,语文 from test
union all
select student,'数学' 科目,数学 from test