sqlite实现行列转换

最近用sqlite做统计，要实现行列转换。

行是：

要达到的效果是：

使用的语句是：

行转成列：

SELECT zldwdm,sum(mj) as total,
sum(CASE WHEN dlbm='01' THEN mj ELSE 0 END) AS '01', 
sum(CASE WHEN dlbm='02' THEN mj ELSE 0 END) AS '02', 
sum(CASE WHEN dlbm='03' THEN mj ELSE 0 END) AS '03', 
sum(CASE WHEN dlbm='04' THEN mj ELSE 0 END) AS '04', 
sum(CASE WHEN dlbm='20' THEN mj ELSE 0 END) AS '20', 
sum(CASE WHEN dlbm='10' THEN mj ELSE 0 END) AS '10', 
sum(CASE WHEN dlbm='11' THEN mj ELSE 0 END) AS '11', 
sum(CASE WHEN dlbm='12' THEN mj ELSE 0 END) AS '12'      
FROM (select substr(zl_dm,1,12) as zldwdm,substr(dl_bm,1,2) as dlbm,sum(gqmj) as mj from LMS_TJPC_NORMAL 
group by substr(zl_dm,1,12),substr(dl_bm,1,2))

GROUP BY zldwdm

再看一个更复杂的语句

SELECT b.xzqmc,a.zldwdm,
    sum(CASE WHEN substr(dlbm,1,2)='01' THEN mj ELSE 0 END) AS '01', 
    sum(CASE WHEN substr(dlbm,1,3)='011' THEN mj ELSE 0 END) AS '011', 
    sum(CASE WHEN substr(dlbm,1,3)='012' THEN mj ELSE 0 END) AS '012', 
    sum(CASE WHEN substr(dlbm,1,3)='013' THEN mj ELSE 0 END) AS '013', 


    sum(CASE WHEN substr(dlbm,1,2)='02' THEN mj ELSE 0 END) AS '02', 
    sum(CASE WHEN substr(dlbm,1,3)='021' THEN mj ELSE 0 END) AS '021', 
    sum(CASE WHEN substr(dlbm,1,3)='