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

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:

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

CodePudding user response：

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.

CodePudding user response：

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