select min(flg1) as flg1, EmpNo,min(SUM_SUM_TO) as SUM_SUM_TO,; max(iif(flg1='A',SUM_SUM_TO,0)) as A1, ; max(iif(flg1='B',SUM_SUM_TO,0)) as B1, ; max(iif(flg1='C',SUM_SUM_TO,0)) as C1, ; max(iif(flg1='D',SUM_SUM_TO,0)) as D1 from TWW GROUP BY EmpNo ORDER BY EmpNo INDEX ON EMPNO TO AA BROW>>hi all,
>>flg1 empno value a1 b1 c1 d1 >>a 1 10 >>b 1 15 >>c 1 20 >>d 1 30 >> >>************************************* >>flg1 empno value a1 b1 c1 d1 >>a 2 10 >>b 2 15 >>d 2 30 >> >>************************************* >> >>flg1 empno value a1 b1 c1 d1 >> >>b 3 12 >> >>*************************************result >> >>flg1 empno value a1 b1 c1 d1 >>a 1 10 10 15 20 30 >>************************* >>a 2 10 10 15 0 30 >>*********************************** >>b 3 12 0 12 0 0 >> >>>>
>select min(flg1) as flg1, EmpNo, >min(Value) as Value, >max(iif(flg1='a',Value,0)) as a1, >max(iif(flg1='b',Value,0)) as b1, >max(iif(flg1='c',Value,0)) as c1, >max(iif(flg1='d',Value,0)) as d1 >from EmpInfo >GROUP BY EmpNo >ORDER BY EmpNo