sqlite实现行列转换_sql

概述最近用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 WH

最近用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,2) as dlbm,sum(gqmj) as mj from LMS_TJPC_norMAL
group by substr(zl_dm,12),2))

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