行转列写法

行转列的简单实现方法
with as 用法
with temp as(
select ‘跳高’ nation ,‘小明’ city,‘第一’ ranking from dual union all
select ‘跳高’ nation ,‘小红’ city,‘第二’ ranking from dual union all
select ‘跳高’ nation ,‘小李’ city,‘第三’ ranking from dual union all
select ‘跳高’ nation ,‘小强’ city,‘第四’ ranking from dual union all
select ‘跳远’ nation ,‘小兰’ city,‘第一’ ranking from dual union all
select ‘跳远’ nation ,‘小张’ city,‘第二’ ranking from dual union all
select ‘跳远’ nation ,‘小花’ city,‘第三’ ranking from dual
)
select * from temp;
With查询语句不是以select开始的，而是以“WITH”关键字开头
可认为在真正进行查询之前预先构造了一个临时表TEMP，之后便可多次使用它做进一步的分析和处理
with 后面跟临时表名

    --临时表temp
    with temp as(
select '跳高' nation ,'小明' city,'第一' ranking from dual union all 
select '跳高' nation ,'小红' city,'第二' ranking from dual union all 
select '跳高' nation ,'小李' city,'第三' ranking from dual union all 
select '跳高' nation ,'小强' city,'第四' ranking from dual union all 
select '跳远' nation ,'小兰' city,'第一' ranking from dual union all 
select '跳远' nation ,'小张' city,'第二' ranking from dual union all 
select '跳远' nation ,'小花' city,'第三' ranking from dual 
)
SELECT nation, MAX (DECODE(ranking,'第一',city,'')) 第一,
MAX (DECODE(ranking,'第二',city,'')) 第二,
MAX (DECODE(ranking,'第三',city,'')) 第三,
MAX (DECODE(ranking,'第四',city,'')) 第四
FROM temp GROUP BY nation