关于表中列行数据转换和组合的问题

 
 这是在坛子里一名网友发布的面试题 使用SQL语句把表一转为表二 我总结了下几位回帖人的答案 并做了测试 把比较好的贴上来  

select t.stname as name, '英语' as subject ,t.english as score from course t 
union 
select m.stname as name,'数学' as subject ,m.math as score from course m 

 

 

select st.stname as name, 
case subject 
when 1 then '数学' 
when 2 then '英语' 
end as subject, 
case subject 
when 1 then st.math
when 2 then st.english
end as grade
from course  st , (select level subject from dual connect by level <= 2) tmp; 

 

 

又找了一个题来练手

 

 

select country as 国家,
sum(case when c.sex=1 then population else 0 end) as 男,
sum(case when c.sex=2 then population else 0 end) as 女
from count c group by country

 

 题3

 

select p_id,
sum(case when s_id=1 then p_num else 0 end) as s1_id,
sum(case when s_id=2 then p_num else 0 end) as s2_id,
sum(case when s_id=3 then p_num else 0 end) as s3_id
from mypro c 
group by p_id