oracle 行转列~列转行（几种方法）

电玩女神 2023-03-01 05:51 27阅读 0赞

工作中，我们经常会碰到行转列的情况

这里我介绍几种简单的方法--行转列

1.oracle的pivot函数

原表

![format_png][]

使用pivot函数：

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,ranking from temp）pivot (max(city) for ranking in ('第一' as 第一,'第二' AS 第二,'第三' AS 第三,'第四' AS 第四));

![format_png 1][]

说明：pivot（聚合函数 for 列名 in（类型）），其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 select distinct ranking from temp

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

SELECT * FROM [StudentScores] /*数据源*/
    AS P
    PIVOT 
    (
        SUM(Score/*行转列后 列的值*/) FOR 
        p.Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)
    ) AS T

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

2.使用max结合decode函数

原表

![format_png][]

使用max结合decode函数

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, '')) as 第一,  
max(decode(ranking, '第二', city, '')) as 第二,  
max(decode(ranking, '第三', city, '')) as 第三,  
max(decode(ranking, '第四', city, '')) as 第四  
from temp group by nation;

![format_png 1][]

说明：decode的用法：**decode**(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下：

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

IF 条件=值1 THEN
    　　　　RETURN(翻译值1)
    ELSIF 条件=值2 THEN
    　　　　RETURN(翻译值2)
    　　　　......
    ELSIF 条件=值n THEN
    　　　　RETURN(翻译值n)
    ELSE
    　　　　RETURN(缺省值)
    END IF

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

3.使用max结合case when 函数

原表

![format_png 2][]

使用max结合case when 函数

select case when grade\_id='1' then '一年级'

when grade\_id='2' then '二年级'

when grade\_id='5' then '五年级'

else null end "年级",

max(case when subject\_name='语文'  then max\_score

else 0 end) "语文" ,

max(case when subject\_name='数学'  then max\_score

else 0 end) "数学" ,

max(case when subject\_name='政治'  then max\_score

else 0 end) "政治"

from dim\_ia\_test\_ysf

group by

case when grade\_id='1' then '一年级'

when grade\_id='2' then '二年级'

when grade\_id='5' then '五年级'

else null end

![format_png 3][]

**下面是介绍列转行方法--列转行**

原表

![format_png 4][]

with temp as(  
select '四川省' nation ,'成都市' 第一,'绵阳市' 第二,'德阳市' 第三,'宜宾市' 第四  from dual union all  
select '湖北省' nation ,'武汉市' 第一,'宜昌市' 第二,'襄阳市' 第三,'' 第四   from dual  
)  
select nation,name,title from  
temp  
unpivot  
(name for title in (第一,第二,第三,第四))t

![format_png 5][]

说明：unpivot（自定义列名/\*列的值\*/ for 自定义列名/\*列名\*/ in（列名））

[format_png]: /images/20230209/1b3018ef1f3941b19ed613648ad11550.png
[format_png 1]: /images/20230209/12b46e34d3e449c589d6049096af4321.png
[aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]: /images/20230209/b0310521a12949d4a49c4f228a989991.png
[format_png 2]: /images/20230209/06cb53422f1246b18e48f5ee16e9607b.png
[format_png 3]: /images/20230209/112c098eb4bf4d8ca66d376e398f9ab6.png
[format_png 4]: /images/20230209/248e93fe230445c7ad7462395572fe88.png
[format_png 5]: /images/20230209/428350935fc94220801077b90c317737.png