oracle sql question

[cli]

Hi,

I would appreciate any help with this please !

I am trying to make the sql below more dynamic

SELECT TB_NAT.CL_NAT,
SUM(DECODE(TB_BAT.DE_STATUS,'JR',1,0)) AS JR,
SUM(DECODE(TB_BAT.CL_GENDER_CODE,1,1,0)) MALE,
SUM(DECODE(TB_BAT.CL_GENDER_CODE,2,1,0)) FEMALE
FROM tb_BAT, TB_NAT
WHERE TB_NAT.CL_NAT= tb_BAT.CL_NAT
GROUP BY TB_NAT.CL_NAT, TB_BAT.DE_STATUS


my problem is the second row, what i want is for each different DE_STATUS on TB_BAT for the sum decode to count it as one and then name the column after the status, this way if a new status is added a new column will come back with a count and called by the new status name, is there any way you can loop through the sum decode in this way ?

thanks .. i hope i explained this OK

Cli

 

[lewisp]

Not sure if this is what you want, but you could try:

SELECT TB_NAT.CL_NAT,
TB_BAT.DE_STATUS,
COUNT(*)
SUM(DECODE(TB_BAT.CL_GENDER_CODE,1,1,0)) MALE,
SUM(DECODE(TB_BAT.CL_GENDER_CODE,2,1,0)) FEMALE
FROM tb_BAT, TB_NAT
WHERE TB_NAT.CL_NAT= tb_BAT.CL_NAT
GROUP BY TB_NAT.CL_NAT, TB_BAT.DE_STATUS