CASE WHEN...THEN与DECODE用了这长时间,一直以为case when只能单独使用或与sum一起使用,在测试一个将纵行数据合并到模行时,去发现该行数据并非是数值类型.
在网上搜索了很多却没有发现与非数值型联合使用的情况,但这并非难事,只要与MAX一起使用即可.
下面举二种类型的例子:
例1: DECODE与MAX的联合使用
SELECT DISTINCT EMP_NO,MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'01',CARD_FREQUENCY)) D01,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'02',CARD_FREQUENCY)) D02,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'03',CARD_FREQUENCY)) D03,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'04',CARD_FREQUENCY)) D04,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'05',CARD_FREQUENCY)) D05,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'06',CARD_FREQUENCY)) D06,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'07',CARD_FREQUENCY)) D07,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'08',CARD_FREQUENCY)) D08,
MAX(DECODE(TO_CHAR(WORK_DATE,'DD'),'09',CARD_FREQUENCY)) D09,
MAX(DECODE(TO_CHAR(WORK_