解决group by出来的字段既需要有作为列字段的又需要有作为行字段的报表问题

1.原始表数据结构

ksh	dqdm	kslbdm
1	01	11
2	01	12
3	02	11
4	02	12
5	02	13
6	03	12
7	03	13
8	04	13
9	04	14

2.需要的报表结构

dqdm	11	12	13	14
01	1	1	0	0
02	1	1	1	0
03	0	1	1	0
04	0	0	1	1

3.解决办法：

1）vfp：

select dqdm,
sum(iif(kslbdm='11',1,0)) as 11,
sum(iif(kslbdm='12',1,0)) as 12,
sum(iif(kslbdm='13',1,0)) as 13,
sum(iif(kslbdm='14',1,0))*3 as 14
from table

group by dqdm;

2）其他数据库：

select dqdm,

sum(case kslbdm when '11' then 1 else 0) as 11,

sum(case kslbdm when '12' then 1 else 0) as 12,

sum(case kslbdm when '13' then 1 else 0) as 13,

sum(case kslbdm when '14' then 1 else 0) as 14

from table

group by dqdm;