How SQL can help to get required table format?

I have a query to know about valid and invalid database objects.

I am using this query:

SELECT object_type,Status,
       count(*)
FROM   all_objects
where owner = 'SchemaName'
group by object_type,status
order by object_type;

I want to see the results in this table structure.

I am using Oracle 12.2

Answer

You can use conditional aggregation:

SELECT object_type,
       count(case when status = 'VALID' then object_name end) as valid,
       count(case when status = 'INVALID' then object_name end) as invalid
FROM   all_objects
where owner = 'SchemaName'
group by object_type
order by object_type;

The count() function ignores nulls, so the case expressions give a not-null value when the condition is matched and those are counted. When the condition is not matched the implied else null (which you can add explicitly of course) gives null, and those are not counted.

In an environment I happen to be in that gives output:

OBJECT_TYPE                  VALID    INVALID
----------------------- ---------- ----------
INDEX                          580          0
PACKAGE                         31          0
PROCEDURE                        1          0
SEQUENCE                        14          0
TABLE                          570          0
TYPE                            12          1
VIEW                             9          1