sql语句将行列进行转换

今天学习了一下如何将一张表中的某列数据由多行改成多列的写法。

如下面：pay_charge表中（为方便假设只有二列wg_user_no户号,price单价）
同一个户号wg_user_no 在表中可能存在二行不同的price值，如
wg_user_no      price
10111011          1.58
10111011          2.37

现在要将它变为：
wg_user_no    price1     price2
10111011         1.58         2.37

代码如下：

--原始表
select wg_user_no,price from pay_charge

--第1步  创建伪列 
select wg_user_no,
case when price= '1.58' then price else '0' end L1,
case when price= '2.37' then price else '0' end L2
 from
(
select wg_user_no,price from pay_charge  where length(wg_user_no)=10 and price in('1.58','2.37') order by wg_user_no
) order by wg_user_no

--第2步  分组求和
select wg_user_no,sum(L1) l1,sum(L2) l2
from
(
select wg_user_no,
case when price= '1.58' then price else '0' end L1,
case when price= '2.37' then price else '0' end L2
 from
(
select wg_user_no,price from pay_charge  where length(wg_user_no)=10 and price in('1.58','2.37') order by wg_user_no
) ) group by wg_user_no having sum(L2)>0 

;

--改case为decode函数  
select wg_user_no,sum(L1) l1,sum(L2) l2
from
(
select wg_user_no,
decode(price,'1.58',price,'0') L1,
decode(price,'2.37',price,'0') L2
 from
(
select wg_user_no,price from pay_charge  where length(wg_user_no)=10 and price in('1.58','2.37') order by wg_user_no
) ) group by wg_user_no having sum(L2)>0 

1