Hi,
I have a table with below structure
| Transaction Date | Country | Division | Entity | Flag | Total |
|---|
| 05-Jan-2015 | GB | 11 | CR | New Errors | 5 |
| 05-Jan-2015 | GB | 11 | Spend | New Errors | 5 |
I need to display it in below format
| Transaction Date | Country | Division | CR-New Errors | Spend-New Errors | Total-New Errors |
|---|
| 05-Jan-2015 | GB | 11 | 5 | 5 | 10 |
If you the see the above format, I want the rows to be transposed to columns based on the FLAG value. One flag entry like "New Errors" will have a multiple Entity (Cr, Spend)
Based on that , we need to group for Transaction Date, Country, Division and show the data.
Sum of the Total Errors in a Particular Flag needs to be displayed in a column.
I have tried below query to transpose the columns.
select t.transaction_dt , t.cntry_id, t.div_id,
nvl(max(decode(t.entity||'-'||t.flag,'CR-New Errors',t.TOTAL)),0) "CR-New Errors",
nvl(max(decode(t.entity||'-'||t.flag,'Spend-New Errors',t.TOTAL)),0) "Spend-New Errors"
from table t
group by t.transaction_dt , t.cntry_id, t.div_id;
In the above query how to get the sum of the total columns for this flag type.
Please help.