html行列转换ds,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