I try to get the grouping elements as column and the count as row elements.
Possible Status = IO, NIO, OPEN, CLOSED, CANCELLED
Possible Types = TypeA, TypeB, typeC, typeD
MyTable (date, status, type, count)
2014-06-27,OPEN,typeA, 1
2014-07-01,OPEN,typeA, 1
2014-07-02,IO,typeB, 2
2014-07-02,IO,typeC, 3
2014-07-02,NIO,typeC, 4
The result should look like. (Groupping on date and status)
date,status,typeA_Count, typeB_Count, typeC_Count, typeD_Count
2014-06-27,OPEN,1, 0, 0, 0
2014-07-01,OPEN,1, 0, 0, 0
2014-07-02,IO, 0, 2, 3, 0
2014-07-02,NIO, 0, 0, 4, 0
A simple way is using CASE/GROUP BY to add up the values;
SELECT "date", "status",
SUM(CASE WHEN "type"='typeA' THEN "count" ELSE 0 END) typeA_count,
SUM(CASE WHEN "type"='typeB' THEN "count" ELSE 0 END) typeB_count,
SUM(CASE WHEN "type"='typeC' THEN "count" ELSE 0 END) typeC_count,
SUM(CASE WHEN "type"='typeD' THEN "count" ELSE 0 END) typeD_count
FROM mytable
GROUP BY "date", "status"
ORDER BY "date", "status"
An Oracle SQLfiddle to test with, although DB2 should run the same without problems.