sqlite实现行列转换

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

行是：

要达到的效果是：

使用的语句是：

行转成列：

GROUP BY zldwdm

再看一个更复杂的语句

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


sum(CASE WHEN substr(dlbm,2)='02' THEN mj ELSE 0 END) AS '02',3)='021' THEN mj ELSE 0 END) AS '021',3)='022' THEN mj ELSE 0 END) AS '022',3)='023' THEN mj ELSE 0 END) AS '023',2)='03' THEN mj ELSE 0 END) AS '03',3)='031' THEN mj ELSE 0 END) AS '031',3)='032' THEN mj ELSE 0 END) AS '032',3)='033' THEN mj ELSE 0 END) AS '033',2)='04' THEN mj ELSE 0 END) AS '04',3)='041' THEN mj ELSE 0 END) AS '041',3)='042' THEN mj ELSE 0 END) AS '042',3)='043' THEN mj ELSE 0 END) AS '043',2)='20' THEN mj ELSE 0 END) AS '20',3)='201' THEN mj ELSE 0 END) AS '201',3)='202' THEN mj ELSE 0 END) AS '202',3)='203' THEN mj ELSE 0 END) AS '203',3)='204' THEN mj ELSE 0 END) AS '204',3)='205' THEN mj ELSE 0 END) AS '205',2)='10' THEN mj ELSE 0 END) AS '10',3)='101' THEN mj ELSE 0 END) AS '101',3)='102' THEN mj ELSE 0 END) AS '102',3)='104' THEN mj ELSE 0 END) AS '104',3)='105' THEN mj ELSE 0 END) AS '105',3)='106' THEN mj ELSE 0 END) AS '106',3)='107' THEN mj ELSE 0 END) AS '107',2)='11' THEN mj ELSE 0 END) AS '11',3)='111' THEN mj ELSE 0 END) AS '111',3)='112' THEN mj ELSE 0 END) AS '112',3)='113' THEN mj ELSE 0 END) AS '113',3)='114' THEN mj ELSE 0 END) AS '114',3)='115' THEN mj ELSE 0 END) AS '115',3)='116' THEN mj ELSE 0 END) AS '116',3)='117' THEN mj ELSE 0 END) AS '117',3)='118' THEN mj ELSE 0 END) AS '118',3)='119' THEN mj ELSE 0 END) AS '119',2)='12' THEN mj ELSE 0 END) AS '12',3)='122' THEN mj ELSE 0 END) AS '122',3)='123' THEN mj ELSE 0 END) AS '123',3)='124' THEN mj ELSE 0 END) AS '124',3)='125' THEN mj ELSE 0 END) AS '125',3)='126' THEN mj ELSE 0 END) AS '126',3)='127' THEN mj ELSE 0 END) AS '127'

FROM (select substr(zl_dm,3) as dlbm,sum(gqmj) as mj from LMS_TJPC_NORMAL
group by substr(zl_dm,3)) a,xzq b where a.zldwdm=b.xzqdm
GROUP BY a.zldwdm

再看一个例子：

SELECT b.xzqmc,sum(a.mj) as total,
sum(CASE WHEN QSXZ='G' THEN mj ELSE 0 END) AS 'Gtotal',
sum(CASE WHEN QSXZ='J' THEN mj ELSE 0 END) AS 'Jtotal',
sum(CASE WHEN dlbm='01' THEN mj ELSE 0 END) AS '01',
sum(CASE WHEN dlbm='01' and QSXZ='G' THEN mj ELSE 0 END) AS 'G01',
sum(CASE WHEN dlbm='01' and QSXZ='J' THEN mj ELSE 0 END) AS 'J01',
sum(CASE WHEN dlbm='02' THEN mj ELSE 0 END) AS '02',
sum(CASE WHEN dlbm='02' and QSXZ='G' THEN mj ELSE 0 END) AS 'G02',
sum(CASE WHEN dlbm='02' and QSXZ='J' THEN mj ELSE 0 END) AS 'J02',
sum(CASE WHEN dlbm='03' THEN mj ELSE 0 END) AS '03',
sum(CASE WHEN dlbm='03' and QSXZ='G' THEN mj ELSE 0 END) AS 'G03',
sum(CASE WHEN dlbm='03' and QSXZ='J' THEN mj ELSE 0 END) AS 'J03',
sum(CASE WHEN dlbm='04' THEN mj ELSE 0 END) AS '04',
sum(CASE WHEN dlbm='04' and QSXZ='G' THEN mj ELSE 0 END) AS 'G04',
sum(CASE WHEN dlbm='04' and QSXZ='J' THEN mj ELSE 0 END) AS 'J04',
sum(CASE WHEN dlbm='20' THEN mj ELSE 0 END) AS '20',
sum(CASE WHEN dlbm='20' and QSXZ='G' THEN mj ELSE 0 END) AS 'G20',
sum(CASE WHEN dlbm='20' and QSXZ='J' THEN mj ELSE 0 END) AS 'J20',
sum(CASE WHEN dlbm='10' THEN mj ELSE 0 END) AS '10',
sum(CASE WHEN dlbm='10' and QSXZ='G' THEN mj ELSE 0 END) AS 'G10',
sum(CASE WHEN dlbm='10' and QSXZ='J' THEN mj ELSE 0 END) AS 'J10',
sum(CASE WHEN dlbm='11' THEN mj ELSE 0 END) AS '11',
sum(CASE WHEN dlbm='11' and QSXZ='G' THEN mj ELSE 0 END) AS 'G11',
sum(CASE WHEN dlbm='11' and QSXZ='J' THEN mj ELSE 0 END) AS 'J11',
sum(CASE WHEN dlbm='12' THEN mj ELSE 0 END) AS '12',
sum(CASE WHEN dlbm='12' and QSXZ='G' THEN mj ELSE 0 END) AS 'G12',
sum(CASE WHEN dlbm='12' and QSXZ='J' THEN mj ELSE 0 END) AS 'J12'
FROM (select substr(zl_dm,2) as DLBM,
case when qs_xz < '30' then 'G' else 'J' end as QSXZ,sum(gqmj) as mj
from LMS_TJPC_NORMAL
group by substr(zl_dm,2),
case when qs_xz < '30' then 'G' else 'J' end) a,xzq b
where a.zldwdm=b.xzqdm GROUP BY a.zldwdm

由此可见sqlite也有类似于Oracle的decode（建议在oracle里也用case when then这样的句子，便于不同数据库移植）

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!