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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s