数据业务札记03：行转列进阶-包含字符串数据的行列转换

在实际业务中，我们常会采用sum （case when） group by 的语法来实现行转列。行转列的好处在于对逻辑主键进行关联时，我们可以大大压缩关联后的行数，

仅用数列字段换来多行记录。而常规的行转列中的聚合函数sum仅能处理字段为数值类型的数据。如果我们需要对字符串类型数据进行行转列处理，则需要一定

的技巧。

首先我们来看一个常规的数值类型行转列的例子：

我们有一张学生各科成绩的表

SELECT
	userid,
	SUM( CASE subjectName WHEN '语文' THEN score ELSE 0 END ) '语文',
	SUM( CASE subjectName WHEN '数学' THEN score ELSE 0 END ) '数学',
	SUM( CASE subjectName WHEN '英语' THEN score ELSE 0 END ) '英语',
	SUM( CASE subjectName WHEN '政治' THEN score ELSE 0 END ) '政治' 
FROM
	tb_score 
GROUP BY
	userid;

行转列结果如下：

可以看到这个例子中，我们需要转列的记录是数值型的成绩，字段名则是有限的科目名称。下面我们来看一个更复杂的例子，我们目前有一个记录客户id，

物品id，物品价格，价格排序的表，该表记录的是客户物品价格前五的记录。

我们现在要对物品名称、物品价格进行转列。类似的，对于数值类型的物品价格，我们可以用sum （case when） group by 的语法进行处理。但是对于

字符串类型的物品id，sum函数则无法进行处理。下面先展示对物品价格进行行转列处理的代码和结果：

select cu_id,
sum(case when pricerank=1 then item_price else 0 end) rank1_price,

sum(case when pricerank=2 then item_price else 0 end) rank2_price,

sum(case when pricerank=3 then item_price else 0 end) rank3_price,

sum(case when pricerank=4 then item_price else 0 end) rank4_price,

sum(case when pricerank=5 then item_price else 0 end) rank5_price

from 
(select cu_id,item_code,item_price,row_number()over(partition by cu_id order by item_price desc) as pricerank 
from cust_price_info) a
group by cu_id

行转列结果：

下面，我们采用一点小技巧，在聚合函数中，我们可以找到max、min是可以兼容字符串数据、数值型数据的。因此这里我们考虑用max来替换sum函数。

下面是具体代码：

select cu_id,
max(case when pricerank=1 then item_code else '0' end) rank1_item_code,
sum(case when pricerank=1 then item_price else 0 end) rank1_price,
max(case when pricerank=2 then item_code else '0' end) rank2_item_code,
sum(case when pricerank=2 then item_price else 0 end) rank2_price,
max(case when pricerank=3 then item_code else '0' end) rank3_item_code,
sum(case when pricerank=3 then item_price else 0 end) rank3_price,
max(case when pricerank=4 then item_code else '0' end) rank4_item_code,
sum(case when pricerank=4 then item_price else 0 end) rank4_price,
max(case when pricerank=5 then item_code else '0' end) rank5_item_code,
sum(case when pricerank=5 then item_price else 0 end) rank5_price

from 
(select cu_id,item_code,item_price,row_number()over(partition by cu_id order by item_price desc) as pricerank 
from cust_price_info) a
group by cu_id

可以看到，使用max来对字符串类型的物品代码进行处理时，我们是先限定了物品价格的排名，然后如果物品代码比字符串‘0’大，那么max函数返回的就是给定

物品价格排名后，物品代码的结果。值得注意的是，虽然min函数也具有兼容字符串类型和数值类型数据的功能，但在上述代码设定下，给定物品价格的排名，任意

字符串都将大于字符串’0‘，因此最后返回的结果也总会是’0‘，最后将得不到我们需要的结果。除非把字符串零改为一个非常大的字符串，例如’99999999999‘，但是

这样处理下我们会浪费不少内存。因此还是建议是哟max（case when ... then...else ’0‘ end）group by 的用法来实现字符串数据的行转列处理。

下面是我们使用上述逻辑实现的带字符串数据的行转列处理结果。