> Ideally I'd generate a table with a column for every month: > > nuar ak sex jan07 feb07 ... dez11
You may need a query with an outer and inner part. The inner part is a query that provides you with the base data which is summarised in the outer part.
I couldn't read your query but if something like this worked:
SELECT animal ,sex ,tochar(dater,'YYDD') as month FROM sourcetable;
you can nest the query to get the sort of result you seem to need, as follows:
SELECT x.animal ,x.sex ,sum(case when x.month='012001' then 1 else 0 end) as '012001' ,sum(case when x.month='022001' then 1 else 0 end) as '022001' ,sum(case when x.month='032001' then 1 else 0 end) as '032001' ... FROM ( SELECT animal ,sex ,tochar(dater,'MMYY') as month FROM sourcetable; ) x GROUP BY x.animal ,x.sex; -- Rory Campbell-Lange rory@campbell-lange.net
Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England No. 04551928