sqlite实现行列转换

孟哥1989

已于 2022-03-10 15:59:01 修改

阅读量1.1w

点赞数 1

分类专栏：数据库相关文章标签： sql

于 2014-03-07 17:02:39 首次发布

本文链接：https://blog.csdn.net/liumengcheng/article/details/20720729

版权

这篇博客介绍了如何使用SQLite进行行列转换，通过CASE WHEN语句实现数据的聚合和条件筛选，将行转换为列，展示了多个具体的SQL语句示例，包括简单的行列转换和更复杂的情况，涉及到数据的统计和分类。这些技巧有助于在SQLite中进行数据处理和分析，提高数据报表的制作效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近用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)='

最低0.47元/天解锁文章