kettle行转列和列转行的使用_数据库函数 行转列 列转行 原理实现分析以及基础语法实现...

行转列与列转行的操作，在数据查询中还是用得比较多的。可以将列名转换为列值，也可以将列值转换为列名。使相同的数据以多姿态的形式呈现出来，让用户能更直观的感受数据。

行转列：简单表演示

先来看看效果：

这是原表的数据： select * from city3 order by nation,ranking;

我们将“ranking”列作为列名，“city”列作为列值，开始行转列操作：

语法格式： TABLE_SOURCE

PIVOT(

聚合函数（value_column）

FOR pivot_column

IN(<column_list>)

)

具体实现：select * from city3 pivot (max(city) for ranking in ('第一','第二','第三','第四'));

这样是不是感觉清晰了很多。但这样写只能用于本次查看。如果此时你将这个结果集作为一个内建视图，用于另外的查询。或者直接将这个查询结果新建为一个表，那么就会有新的问题产生。

注意看结果集的列名，'第一' '第二' 。这些列名是包含在“单引号”中的，你在查询时，是无法使用这样的列名的。因为你这么写，数据库会把它当成一个字符串。然后无法匹配你的列，是不是很神奇？

那我们要怎么做呢？我们可以在行转列的时候，为生成的列指定“列别名”。和普通列别名一样，使用as关键字指定即可。

select * from city3 pivot (max(city) for ranking in ('第一' as 第一,'第二' as 第二,'第三' as 第三,'第四' as 第四));

多列表演示

下面我们来看看多列的： select * from city order by nation,num;

采用行转列后：

select * from city pivot (max(city) for ranking in ('第一' as 第一,'第二' as 第二,'第三' as 第三,'第四' as 第四));

是不是很神奇？看起来有点晕了，其实我们仔细分析就能看出其中的原理：

1.行转列操作的数据只能是2列，一列作为列名，一列作为列值。

2.其他的列都会进行分组操作。有点像group by nation,num;

知道这2个规律了，我们就可以来进行行转列的基础语法实现了。

行转列基础语法实现

分析：其实就是分组函数加上内容选择。

基础语法实现：

select nation,num,

max(case ranking when '第一' then city else null end) as 第一,

max(case ranking when '第二' then city else null end) as 第二,

max(case ranking when '第三' then city else null end) as 第三,

max(case ranking when '第四' then city else null end) as 第四

from city group by nation,num;

我们可以看到，第一，第二，第三，第四几列，都使用了内容选择，并且因为语句使用了group by字句。所以内容选择必须嵌套在组函数中。这也解释了，为什么函数语法中，需要我们加上组函数了。

列转行：简单表演示

列转行，其实就是行专列的逆推导。

这是原表的数据： select * from city4;

我们将列名转换为列值，开始列转行操作

语法格式： TABLE_SOURCE

UNPIVOT(

value_column

FOR pivot_column

IN(<column_list>)

)

具体实现：select * from city4 unpivot (city_name for rank_num in(第一,第二,第三,第四));

注意：这里的列名是匹配具体的列，不能加单引号。

如果之前列转行时，我们没有指定不带单引号的列别名的话，我们将无法完成逆推导。

这样就将数据还原了回来，生成的列名，其实就是我们参数中给定的名字。

多列表演示

下面我们来看看多列的： select * from city2;

采用列转行后：

select * from city2 unpivot (city_name for rank_num in(第一,第二,第三,第四));

数据同样还原了回来，下面我们来看看基础语法的实现。

列转行基础语法实现

分析：分组函数的逆推导，我们可以查询单个“组”，然后用union来连接结果。

基础语法实现：

select nation,num,'第一' as rank_num,第一 as city_name from city2 where 第一 is not null union

select nation,num,'第二' as rank_num,第二 as city_name from city2 where 第二 is not null union

select nation,num,'第三' as rank_num,第三 as city_name from city2 where 第三 is not null union

select nation,num,'第四' as rank_num,第四 as city_name from city2 where 第四 is not null;

上面我们需要注意，在多列表中，列值可能存在空值，所以我们需要在where字句中，将空值进行过滤。