with temp as(

     select '湖北省' province,'武汉市' city,'第一' ranking from dual union all

     select '湖北省' province,'孝感市' city,'第二' ranking from dual union all

     select '湖北省' province,'宜昌市' city,'第三' ranking from dual union all

     select '湖北省' province,'襄阳市' city,'第四' ranking from dual union all

     select '湖南省' province,'长沙市' city,'第一' ranking from dual union all

     select '湖南省' province,'岳阳市' city,'第二' ranking from dual union all

     select '湖南省' province,'衡阳市' city,'第三' ranking from dual

)

--使用pivot

select * from (select province,city,ranking from temp) pivot(min(city) for ranking in

('第一' as 第一,'第二' as 第二,'第三' as 第三,'第四' as 第四))

--不使用pivot进行行转列

select province,

max(decode(ranking,'第一',city,'')) as 第一,

max(decode(ranking,'第二',city,'')) as 第二,

max(decode(ranking,'第三',city,'')) as 第三,

max(decode(ranking,'第四',city,'')) as 第四

from temp group by province