Hi team,
I have below data in the table, Primary Key is Item and Location and my Startdate is always 1st day of the month only. I have three columns PERIOD1 PERIOD2 PERIOD3 and I want to get the count of number of columns which are having non - zero (can be null or zero values).
so for Item1 and Loc1 expected output is 2 (10 and 20) are non - zero values.
so for Item2 and Loc2 expected output is 1 (20) is non - zero values.
Present output
| | dd/mm/yyyy | | | |
| Item | Loc | Startdate | PERIOD1 | PERIOD2 | PERIOD3 |
| Item1 | Loc1 | 1/8/2015 | 10 | 0 | 20 |
| Item2 | Loc2 | 1/9/2015 | 20 | null | null |
Expected output:
| | | dd/mm/yyyy | | | | |
| Item | Loc | Startdate | PERIOD1 | PERIOD2 | PERIOD3 | Count of Non Zero |
| Item1 | Loc1 | 1/8/2015 | 10 | 0 | 20 | 2 |
| Item2 | Loc2 | 1/9/2015 | 20 | null | null | 1 |
with t1 as
(
select 'Item1' item, 'loc1' loc, '01/08/2015' startdate, 10 period1, 0 period2, 20 period3 from dual
union all
select 'Item2' item, 'loc2' loc, '01/09/2015' startdate, 20 period1, null period2, null period3 from dual
)
select * from t1;
One way to get the output is do unpivot and get the count, am looking for a different solution without unpiovt.
Thanks in advance.