1、行转列(行变少、列变多)
with temp as(
select '湖北省' nation, '武汉市' city, 'FIRST' rank from dual union all
select '湖北省' nation, '宜昌市' city, 'SECOND' rank from dual union all
select '湖北省' nation, '襄阳市' city, 'THIRD' rank from dual
)
NATION | CITY | RANK |
---|
湖北省 | 武汉市 | FIRST |
湖北省 | 宜昌市 | SECOND |
湖北省 | 襄阳市 | THIRD |
select nation,
max(decode(rank, 'FIRST', city, '')) first,
max(decode(rank, 'SECOND', city, '')) second,
max(decode(rank, 'THIRD', city, '')) third
from temp group by nation
NATION | FIRST | SECOND | THIRD |
---|
湖北省 | 武汉市 | 宜昌市 | 襄阳市 |
2、列转行(列变少、行变多)
with temp as(
select '湖北省' nation, '武汉市' first, '宜昌市' second, '襄阳市' third from dual
)
NATION | FIRST | SECOND | THIRD |
---|
湖北省 | 武汉市 | 宜昌市 | 襄阳市 |
select nation, city, rank from temp
unpivot (city for rank in (first, second, third))
NATION | CITY | RANK |
---|
湖北省 | 武汉市 | FIRST |
湖北省 | 宜昌市 | SECOND |
湖北省 | 襄阳市 | THIRD |