Oracle row-to-column (using the pivot function)

In daily use, this is often the case where the rows of the database need to be converted into columns, such as

Converted to this time, we need to use the pivot function after Baidu, reference URL http://www.2cto.com/database/201501/367164.html, completed the following operations 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 （select Nation,city,< Span class= "PLN" >ranking from Temp) pivot Span class= "pun" > (max (city for ranking in ( ' first ' as First, ' second ' as second, ' third ' as Span class= "pun" > third, ' IV ' as fourth); In this way, the operation is implemented smoothly, the key function pivot, which uses the following pivot（聚合函数 for 列名 in（类型）） --其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 select distinct ranking from tempOf course, you can also use the pivot function without using the following statement to achieve the same effect 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, ' first ' , City, ") as First, Max (decode ( ranking, ' second ' , City, ") as second, Max (decode ( ranking, ' third ' , City, ") as third, max(decode(ranking, ‘第四‘, city, ‘‘)) as 第四 from temp group by nation;Of course, Oracle also provides the UNPIVOT function, the operation of the column conversion, the project has not been used, there is no research. Reference address: http://blog.csdn.net/xb12369/article/details/39554935 solve long-standing problems, the mood is comfortable, so recorded in this, convenient later view

Oracle row-to-column (using the pivot function)