10 x 10 Matrix using SQL

Hey Friends,

Try this small technique to display your own pattern using a SQL query output.

img01
(Scroll to the bottom of this post for other interesting patterns)

Below are the skeleton steps to achieve.

1. First we need 10×10 matrix.

2. Join two tables, one table with 10 columns and another table with 10 rows.
2a. Say Table with 10 rows as V(Vertical)
select * from (select 1 "1" from dual connect by level <= 10) v;
2b. Say Table with 10 columns as H(Horizontal). This can be easily created
using pivot.
select * from (select level "1" from dual connect by level <= 10)
pivot(sum("1") for "1" in (1,2,3,4,5,6,7,8,9,10)) h;

3.Cartesian join these tables
3a. select * from (select 1 "1" from dual connect by level <= 10) v,
(select level "1" from dual connect by level <= 10) pivot(sum("1")
for "1" in (1,2,3,4,5,6,7,8,9,10)) h;

3b. Now you get a beautiful grid as output as below
img02

4. Now our aim is to ON/OFF these each cell to get desired output
4a. Note that there are 100 cells, so create a display control with 100 bits
and cartesian join with above sql as shown below
select * from (select 1 "1" from dual connect by level <= 10) v,
(select level "1" from dual connect by level <= 10) pivot(sum("1") for
"1" in (1,2,3,4,5,6,7,8,9,10)) h,(select
'1111011110100001001011110100100001010110111101111000000001110001000000000100000000010000000001111000' "DispControl" from dual) Disp;

5. Now comes the part to real switch ON/OFF the cells based on display control
bits
5a. Use decode function to achieve this,
DECODE(SUBSTR(“DispControl”,((rownum-1)*10)+B.”1″,1),1,’|+|’,0,’ ‘) “A”
The above switch on cell with pattern |+| if the corresponding displaycontrol bit is set to
one else blank

6. Here is the final sql
SELECT DECODE(SUBSTR("DispControl",((rownum-1)*10)+h."1",1),1,'|+|',0,' ') "1",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."2",1),1,'|+|',0,' ') "2",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."3",1),1,'|+|',0,' ') "3",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."4",1),1,'|+|',0,' ') "4",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."5",1),1,'|+|',0,' ') "5",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."6",1),1,'|+|',0,' ') "6",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."7",1),1,'|+|',0,' ') "7",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."8",1),1,'|+|',0,' ') "8",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."9",1),1,'|+|',0,' ') "9",
DECODE(SUBSTR("DispControl",((rownum -1)*10)+h."10",1),1,'|+|',0,' ') "10"
FROM
(SELECT 1 "1" FROM dual CONNECT BY level <= 10
) v,
(SELECT level "1" FROM dual CONNECT BY level <= 10
) pivot(SUM("1") FOR "1" IN (1,2,3,4,5,6,7,8,9,10)) h,
(SELECT '1111011110100001001011110100100001010110111101111000000001110001000000000100000000010000000001111000' "DispControl"
FROM dual
) Disp;

Additional Notes:

Replace DispControl bit with following to get new patterns
Pattern 1: UN
'1000100000100010000010001000001000100000111110000000000100010000011001000001010100000100110000010001'

img03.PNG

Pattern 2: Love
'0010000100010100101010001100011000000001100000000110000000010100000010001000010000010010000000110000'

capture

Note: This SQL works well in SQL Developer running on Oracle 11g database

Use http://www.tutorialspoint.com/oracle_terminal_online.php ; this is an online free oracle 11g terminal to test these sql. If you face any difficulty please share it through the comments.

 

Rank(), Dense_Rank() and Row_Number() SQL

Let’s see below example,

Consider our education department has below data and they wanted to find the ranks of students State wise-Z, School wise-Y and Class wise-X.

Wonder how to achieve this in single SQL.

Name School Class Marks Class Rank
X
School Rank
Y
State Rank
Z
Alice Central School A Division 456 1 1 2
Bob Central School A Division 345 2 4 7
Charlie Central School B Division 401 2 3 5
Eve Central School B Division 445 1 2 3
Mallet Liberty School A Division 323 2 4 8
Oscar Liberty School A Division 433 1 2 4
Peggy Liberty School B Division 401 2 3 5
Sybil Liberty School B Division 467 1 1 1

We can achieve this using Rank(), Group by and Partition by concept.

Assume table name as “Scores”

1. Let’s starts with State wise rank for each student

To find State rank we have to order the students marks in descending order and call rank()
query:
select s.*, rank() over (order by Marks desc) “State Rank” from Scores s;

Simple right!!!

2. Let’s see how to find School wise rank for each student

Since multiple school data is in the same table “Scores” we have to partition data first based on school and  then order the Marks in descending order
query:
select s.*, rank() over (partition by School order by Marks desc) “School Rank” from Scores s;

3. Let’s see how to find Class wise rank for each student

It’s very similar to above, here we have to classify data based on School&Class first then order Marks by descending order.
query:
select s.*, rank() over (partition by School, Class order by Marks desc) “Class Rank” from Scores s;

In single query:
select s.*, rank() over (order by Marks desc) “State Rank”, rank() over (partition by School order by Marks desc) “School Rank”, rank() over (partition by School, Class order by Marks desc) “Class Rank” from Scores s;

Note: Marks column should be a number or integer field, if not use to_number(Marks) method then apply order by

We have two common methods like rank(), they are dense_rank() and row_number()

a. dense_rank() – Have you noticed in above example Charlie and Peggy  have the same score and so share same State Rank(5th rank). Due to this 6th rank is skipped and Bob got (7th rank). If you do not want to skip 6th rank use dense_rank() instead of rank().

b. row_number() – In the above example we saw Charlie and Peggy have same score and shared same rank. If we use row_number() one may get 5th and other may get 6th based on the order in which they appear in the query output, since row number just assign the sequential value based on some alphabetical order. This should not be used if you want to find the correct rank.