How to select a matrix of distinct values in PL/SQL using “where” expression for each cell

Question

I’m interested to analyze a table in Oracle which has the following form:

Column A	Column B	Column C
11	0	A
14	1	7
45	3	3
64	3	3
80	7	3

IMPORTANT: Column A is varchar2(10) Column B is Number(1) Column C is Char(1)

A is primary key 
B has only {0,1,3,7} distinct   values
C has only {‘0’,’1’,’2’,’3’,’4’,’5’,’6’,’7’,’8’,’9’,’A’} distinct values 

I want to make select command that will show matrix of distinct values where each cell will be result of expression such as

select count(*) from table where  B = 1 and C = ‘A’

It means that we will get this kind of matrix:

	0	1	3	7
0
1
2
3
4
5
6
7
8
9
A

For example cell(B = 3, C = ‘A’) will contain result of this command:

select count(*) from table where  B = 3 and C = ‘A’

Is it possible to make this only with select command or I need to create new tables and make scripts?

I have tried to make it manually with this command

select count(*) from table where  B = 1 and C = ‘A’

Just alter B and C values in this command myself and it makes me tired because it takes a lot of time. This table is enormous for this king of commands. So, I’m interested to use universal command that helps me to analyze this table

Answer 1

You can create additional table (or subquery or temporal table) for holding the rows:

CREATE TABLE subquery (col Char(1));

INSERT INTO subquery(col) VALUES ('0');
INSERT INTO subquery(col) VALUES ('1');
INSERT INTO subquery(col) VALUES ('2');
INSERT INTO subquery(col) VALUES ('3');
INSERT INTO subquery(col) VALUES ('4');
INSERT INTO subquery(col) VALUES ('5');
INSERT INTO subquery(col) VALUES ('6');
INSERT INTO subquery(col) VALUES ('7');
INSERT INTO subquery(col) VALUES ('8');
INSERT INTO subquery(col) VALUES ('9');
INSERT INTO subquery(col) VALUES ('A');

Then, simply write:

SELECT A.col
     ,SUM(CASE WHEN B.Column_B = 0 THEN 1 ELSE 0 END) AS Col0
     ,SUM(CASE WHEN B.Column_B = 1 THEN 1 ELSE 0 END) AS Col1
     ,SUM(CASE WHEN B.Column_B = 3 THEN 1 ELSE 0 END) AS Col3
     ,SUM(CASE WHEN B.Column_B = 7 THEN 1 ELSE 0 END) AS Col7
FROM subquery A
LEFT JOIN example_table B
  ON A.col = B.Column_C
GROUP BY A.col
ORDER BY A.col

Here is the full working example.

Answer 2

I found it simple:

SELECT B,C, count(*) from table group by B,C

It will not show matrix, but this vector can be used to create matrix by using B and C values as indexes and count(*) as value