1、目前常用的竖表转横表方法为:decode,case when,decode适用于条件比较单一的业务场景,case when 适用于条件限制比较的业务场景,但是这两种写法都必需有一个前天,就是列数据是固定的,可以通过判定进行转换成行形式的表头。比如常用举例的学生的各个学科数据,都是固定的chinese,math,english等等。
2、但是当数据是不固定的时候,很难通过判断相等,将表数据进行竖表转横表
具体业务场景如下hengz
项目project_id | 年份 predict_year | 预算_predict_cost |
1 | 2018 | 200 |
1 | 2019 | 300 |
1 | 2020 | 400 |
2 | 2008 | 55 |
2 | 2009 | 66 |
2 | 2010 | 77 |
希望转换出来的结果是:
项目project_id | 第一年 | 第二年 | 第三年 |
1 | 2018:200 | 2019:300 | 2020:400 |
2 | 2008: | 2009:55 | 2010:77 |
我们注意到:按照普通的竖表转横表,首先predict_year就不满足基本条件,因为项目1第一年为2018,项目2第一年为2008,为了适用,我们必须把年份转换为通用的‘第一年’,‘第二年’,‘第三年’,但是结果如果只有预算,查询的数据也会让人不明白,因此做一个年份与预算的拼接作为结果。如何写这个sql?
核心:找到所有项目的最小年份,然后依次累加得到以后依次的年份
这里做了个巧妙的子查询连表,以
(select aa.PROJECT_RECID, min(aa.predict_year) as minq
from TPROJECT aa
group by aa.PROJECT_RECID) b --查询项目最小年份作为子表
作为子表与项目表数据进行连接查询,并在select每项中做子查询
最终sql:
select distinct a.PROJECT_RECID,--去重
(select c.predict_year || '年:' ||c.predict_cost
from TPROJECT c
where c.PROJECT_RECID = a.PROJECT_RECID
and c.predict_year = b.minq) predict_year1,--根据主子表,作为条件 查询结果进行评价
(select c.predict_year || '年:' ||c.predict_cost
from TPROJECT c
where c.PROJECT_RECID = a.PROJECT_RECID
and c.predict_year = (b.minq +1)) predict_year2,
(select c.predict_year || '年:' ||c.predict_cost
from TPROJECT c
where c.PROJECT_RECID = a.PROJECT_RECID
and c.predict_year = (b.minq +2)) predict_year3
from TPROJECT a,
(select aa.PROJECT_RECID, min(aa.predict_year) as minq
from TPROJECT aa
group by aa.PROJECT_RECID) b --查询项目最小年份作为子表
where a.PROJECT_RECID = b.PROJECT_RECID
最终查询结果: