Oracle之行转列笔录

decode(value,if1,then1,if2,then2,if3…eles)
eg: decode(value,‘0’,‘1’,‘2’)
若value为0，则返回1，否则返回2

case key when if1 then value1 when if2 then value2 …else valuen end
eg: case key when ‘0’ then ‘1’ else ‘2’ end
若key为0，则返回1，否则返回2

行转列和列转行是一样，列转行无合适的分组字段可自定义一个常量字段！

数字型行转列：
   select 
     a. name,
     sum(decode(a.subject,'数学',a.score,null)) as 数学，
     sum(decode(a.subject,'语文',a.score,null)) as 语文，
     sum(decode(a.subject,'英语',a.score,null)) as 英语
     from SQL_DEMO a 
    group by a.name 

数字类型列转行2：
    select 
      subject,
      sum(case name when '张三' then score end) as 张三,
      sum(case name when '李四' then score end) as 李四,
      sum(case name when '王五' then score end) as 王五
      from SQL_DEMO 
      group by subject