I am attempting to blend 2 tables together using an Oracle Select statement. Unfortunately, I lack privileges to create tables or procedures.

One is a table with 2 rows and 2 columns and each cell has the following values:

• Column 1 and row 1 value will be C
• Column 1 and row 2 value will be B
• Column 2 and row 1 value will be D
• Column 2 and row 2 value will be A

Here is the cte:

WITH Table_2x2 AS (
    SELECT 'C' AS column1, 'D' AS column2 FROM dual
    UNION ALL
    SELECT 'B' AS column1, 'A' AS column2 FROM dual
)

I have a second table that contains 10 rows and 10 columns. Each cell contains the following values:

• the cells in row 1 incrementally decrease by one starting with 100 and ending with 091.
• The cells in row 2 incrementally decrease by one starting with 090 and ending with 081.
• The pattern continues until row 10, column 10 has 001

Here is the cte:

Table_10x10 AS (
    SELECT 100 AS col1, 099 AS col2, 098 AS col3, 097 AS col4, 096 AS col5, 095 AS col6, 094 AS col7, 093 AS col8, 092 AS col9, 091 AS col10 FROM dual
    UNION ALL
    SELECT 090 AS col1, 089 AS col2, 088 AS col3, 087 AS col4, 086 AS col5, 085 AS col6, 084 AS col7, 083 AS col8, 082 AS col9, 081 AS col10 FROM dual
    UNION ALL
    SELECT 080 AS col1, 079 AS col2, 078 AS col3, 077 AS col4, 076 AS col5, 075 AS col6, 074 AS col7, 073 AS col8, 072 AS col9, 071 AS col10 FROM dual
    UNION ALL
    SELECT 070 AS col1, 069 AS col2, 068 AS col3, 067 AS col4, 066 AS col5, 065 AS col6, 064 AS col7, 063 AS col8, 062 AS col9, 061 AS col10 FROM dual
    UNION ALL
    SELECT 060 AS col1, 059 AS col2, 058 AS col3, 057 AS col4, 056 AS col5, 055 AS col6, 054 AS col7, 053 AS col8, 052 AS col9, 051 AS col10 FROM dual
    UNION ALL
    SELECT 050 AS col1, 049 AS col2, 048 AS col3, 047 AS col4, 046 AS col5, 045 AS col6, 044 AS col7, 043 AS col8, 042 AS col9, 041 AS col10 FROM dual
    UNION ALL
    SELECT 040 AS col1, 039 AS col2, 038 AS col3, 037 AS col4, 036 AS col5, 035 AS col6, 034 AS col7, 033 AS col8, 032 AS col9, 031 AS col10 FROM dual
    UNION ALL
    SELECT 030 AS col1, 029 AS col2, 028 AS col3, 027 AS col4, 026 AS col5, 025 AS col6, 024 AS col7, 023 AS col8, 022 AS col9, 021 AS col10 FROM dual
    UNION ALL
    SELECT 020 AS col1, 019 AS col2, 018 AS col3, 017 AS col4, 016 AS col5, 015 AS col6, 014 AS col7, 013 AS col8, 012 AS col9, 011 AS col10 FROM dual
    UNION ALL
    SELECT 010 AS col1, 009 AS col2, 008 AS col3, 007 AS col4, 006 AS col5, 005 AS col6, 004 AS col7, 003 AS col8, 002 AS col9, 001 AS col10 FROM dual
)

I am trying to blend or merge the tables together so the result is a table with 20 rows and 20 columns. The idea is that each cell from the 10x10 table is split into the 4 quadrants from the 2x2 table. The value for each cell in the 20x20 table is a concatenation of the value from the 10x10 cell with the values from the 2x2 table. For example:

• row 1, column 1: 100C
• row 1, column 2: 100D
• row 2, column 1: 100B
• row 2, column 2: 100A

I have tried multiple approaches, between cross join, unions, pivots, etc. AI has been no help at all. I've tried ChatGTP, Github Copilot and Microsoft Copilot and it never seems to get it, no matter how well I explain the problem, so I am coming back to you humans!

For anyone who is extra ambitious (I am only asking for help on the above question) but, my ultimate goal is to create a query that recognizes a mapsheet grid.

The table is supposed to match the grid. I've been asked to build a query that will allow a user to enter a unique cell value and the query will return the unique value of the cells directly above, below, to the right and left of the given cell.

The mapsheet grid works like this:

1. The overall grid is broken into 4 rows and 4 columns and has values outlined in Level 1 below
2. Each cell form #1 is broken into 4 rows and 4 columns and has values outlined in Level 2 below
3. Each cell from #2 is broken into 4 rows and 4 columns and has values outlined in Level 3 below
4. Each cell from #3 is broken into 3 rows and 4 columns and has values outlined in Level 4 below
5. Each cell from #4 is broken into 10 rows and 10 columns and has values outlined in Level 5 below
6. Each cell from #5 is broken into 2 rows and 2 columns and has values outlined in Level 6 below

Each bottom level cell has a unique identified i.e. 103G06H001B, made up of the following

• Level 1: 3 numbers as outlined below (i.e. 103)
• Level 2: 1 letter as outlined below (i.e. G)
• Level 3: 2 numbers as outlined below (i.e. 06)
• Level 4: 1 letter as outlined below (i.e. H)
• Level 5: 3 numbers as outlined below (i.e. 001)
• Level 6: 1 letters as outlined below (i.e. B)

Note, in the images below, point #4 reads incorrectly. It should say "Blocks are further divided into 12 Unit letters." Not 16.

So, for example, for cell 103G06H001B, cells adjacent would be

• Above cell: 103G06H001C
• Below cell: 103G06A091C
• Right cell: 103G06H001A
• Left Cell: 103G06H002A

I am attempting to blend 2 tables together using an Oracle Select statement. Unfortunately, I lack privileges to create tables or procedures.

One is a table with 2 rows and 2 columns and each cell has the following values:

• Column 1 and row 1 value will be C
• Column 1 and row 2 value will be B
• Column 2 and row 1 value will be D
• Column 2 and row 2 value will be A

Here is the cte:

WITH Table_2x2 AS (
    SELECT 'C' AS column1, 'D' AS column2 FROM dual
    UNION ALL
    SELECT 'B' AS column1, 'A' AS column2 FROM dual
)

I have a second table that contains 10 rows and 10 columns. Each cell contains the following values:

• the cells in row 1 incrementally decrease by one starting with 100 and ending with 091.
• The cells in row 2 incrementally decrease by one starting with 090 and ending with 081.
• The pattern continues until row 10, column 10 has 001

Here is the cte:

Table_10x10 AS (
    SELECT 100 AS col1, 099 AS col2, 098 AS col3, 097 AS col4, 096 AS col5, 095 AS col6, 094 AS col7, 093 AS col8, 092 AS col9, 091 AS col10 FROM dual
    UNION ALL
    SELECT 090 AS col1, 089 AS col2, 088 AS col3, 087 AS col4, 086 AS col5, 085 AS col6, 084 AS col7, 083 AS col8, 082 AS col9, 081 AS col10 FROM dual
    UNION ALL
    SELECT 080 AS col1, 079 AS col2, 078 AS col3, 077 AS col4, 076 AS col5, 075 AS col6, 074 AS col7, 073 AS col8, 072 AS col9, 071 AS col10 FROM dual
    UNION ALL
    SELECT 070 AS col1, 069 AS col2, 068 AS col3, 067 AS col4, 066 AS col5, 065 AS col6, 064 AS col7, 063 AS col8, 062 AS col9, 061 AS col10 FROM dual
    UNION ALL
    SELECT 060 AS col1, 059 AS col2, 058 AS col3, 057 AS col4, 056 AS col5, 055 AS col6, 054 AS col7, 053 AS col8, 052 AS col9, 051 AS col10 FROM dual
    UNION ALL
    SELECT 050 AS col1, 049 AS col2, 048 AS col3, 047 AS col4, 046 AS col5, 045 AS col6, 044 AS col7, 043 AS col8, 042 AS col9, 041 AS col10 FROM dual
    UNION ALL
    SELECT 040 AS col1, 039 AS col2, 038 AS col3, 037 AS col4, 036 AS col5, 035 AS col6, 034 AS col7, 033 AS col8, 032 AS col9, 031 AS col10 FROM dual
    UNION ALL
    SELECT 030 AS col1, 029 AS col2, 028 AS col3, 027 AS col4, 026 AS col5, 025 AS col6, 024 AS col7, 023 AS col8, 022 AS col9, 021 AS col10 FROM dual
    UNION ALL
    SELECT 020 AS col1, 019 AS col2, 018 AS col3, 017 AS col4, 016 AS col5, 015 AS col6, 014 AS col7, 013 AS col8, 012 AS col9, 011 AS col10 FROM dual
    UNION ALL
    SELECT 010 AS col1, 009 AS col2, 008 AS col3, 007 AS col4, 006 AS col5, 005 AS col6, 004 AS col7, 003 AS col8, 002 AS col9, 001 AS col10 FROM dual
)

I am trying to blend or merge the tables together so the result is a table with 20 rows and 20 columns. The idea is that each cell from the 10x10 table is split into the 4 quadrants from the 2x2 table. The value for each cell in the 20x20 table is a concatenation of the value from the 10x10 cell with the values from the 2x2 table. For example:

• row 1, column 1: 100C
• row 1, column 2: 100D
• row 2, column 1: 100B
• row 2, column 2: 100A

I have tried multiple approaches, between cross join, unions, pivots, etc. AI has been no help at all. I've tried ChatGTP, Github Copilot and Microsoft Copilot and it never seems to get it, no matter how well I explain the problem, so I am coming back to you humans!

For anyone who is extra ambitious (I am only asking for help on the above question) but, my ultimate goal is to create a query that recognizes a mapsheet grid.

The table is supposed to match the grid. I've been asked to build a query that will allow a user to enter a unique cell value and the query will return the unique value of the cells directly above, below, to the right and left of the given cell.

The mapsheet grid works like this:

1. The overall grid is broken into 4 rows and 4 columns and has values outlined in Level 1 below
2. Each cell form #1 is broken into 4 rows and 4 columns and has values outlined in Level 2 below
3. Each cell from #2 is broken into 4 rows and 4 columns and has values outlined in Level 3 below
4. Each cell from #3 is broken into 3 rows and 4 columns and has values outlined in Level 4 below
5. Each cell from #4 is broken into 10 rows and 10 columns and has values outlined in Level 5 below
6. Each cell from #5 is broken into 2 rows and 2 columns and has values outlined in Level 6 below

Each bottom level cell has a unique identified i.e. 103G06H001B, made up of the following

• Level 1: 3 numbers as outlined below (i.e. 103)
• Level 2: 1 letter as outlined below (i.e. G)
• Level 3: 2 numbers as outlined below (i.e. 06)
• Level 4: 1 letter as outlined below (i.e. H)
• Level 5: 3 numbers as outlined below (i.e. 001)
• Level 6: 1 letters as outlined below (i.e. B)

Note, in the images below, point #4 reads incorrectly. It should say "Blocks are further divided into 12 Unit letters." Not 16.

So, for example, for cell 103G06H001B, cells adjacent would be

• Above cell: 103G06H001C
• Below cell: 103G06A091C
• Right cell: 103G06H001A
• Left Cell: 103G06H002A

• sql
• oracle-database

2 Answers 2

You appear to want to generate all the cells. Generating is as an NxN grid is the wrong approach as you will have far too many columns and will not be able to search them. Instead, if you want to generate all the values, generate them as rows:

SELECT TO_CHAR(10*FLOOR(nts_cell/4)+MOD(nts_cell, 4)+82, 'FM099') AS nts_cell,
       CHR(64+bcgs) AS bcgs,
       TO_CHAR(block, 'FM09') AS block,
       CHR(64+unit_letter) AS unit_letter,
       TO_CHAR(unit_number, 'FM009') AS unit_number,
       CHR(64+cell_letter) AS cell_letter,
       TO_CHAR(10*FLOOR(nts_cell/4)+MOD(nts_cell, 4)+82, 'FM099')
       || CHR(65+bcgs)
       || TO_CHAR(block, 'FM09')
       || CHR(64+unit_letter)
       || TO_CHAR(unit_number, 'FM009')
       || CHR(64+cell_letter) AS code
FROM   (SELECT LEVEL - 1 AS nts_cell FROM DUAL CONNECT BY LEVEL <= 16)
       CROSS JOIN (SELECT LEVEL AS bcgs FROM DUAL CONNECT BY LEVEL <= 16)
       CROSS JOIN (SELECT LEVEL AS block FROM DUAL CONNECT BY LEVEL <= 16)
       CROSS JOIN (SELECT LEVEL AS unit_letter FROM DUAL CONNECT BY LEVEL <= 16)
       CROSS JOIN (SELECT LEVEL AS unit_number FROM DUAL CONNECT BY LEVEL <= 100)
       CROSS JOIN (SELECT LEVEL AS cell_letter FROM DUAL CONNECT BY LEVEL <= 4)

Which outputs:

However, even this is probably a sub-optimal approach.

If you want the neighbours of 103G06H001B then start parsing the string from the right:

• The cell letter is B and we know that to left and right are going to be A cells and above and below are going to be C cells.
• A B cell letter always has the right and above cells in the same unit number so the right cell will be 103G06H001A and the above cell will be 103G06H001C.
• The below of the B cell letter will be in the unit number below. Since the unit number is 001 then below that will be in the top-most unit number of the next unit letter down. The unit letter is H and down from that is A and 091 is below 001 so the below cell is 103G06A091C.
• You can apply a similar logic for the leftwards cell to get to 103G06H002A.

By parsing the string from right-to-left and splitting it up into each of the terms you can calculate what the adjacent cells will be without having to generate a grid and it will be much less computationally intense.

A solution is to create a function (which, in later Oracle versions, can be defined in the query):

WITH FUNCTION left_code(code IN VARCHAR2) RETURN VARCHAR2
IS
  nts_cell_h  VARCHAR2(2) := SUBSTR(code,  1, 2);
  nts_cell_v  VARCHAR2(1) := SUBSTR(code,  3, 1);
  bgcs        VARCHAR2(1) := SUBSTR(code,  4, 1);
  block       VARCHAR2(2) := SUBSTR(code,  5, 2);
  unit_letter VARCHAR2(1) := SUBSTR(code,  7, 1);
  unit_number VARCHAR2(3) := SUBSTR(code,  8, 3);
  cell        VARCHAR2(1) := SUBSTR(code, 11, 1);
  new_cell    VARCHAR2(1);
  new_unit_number VARCHAR2(3);
  new_unit_letter VARCHAR2(1);
BEGIN
  IF cell = 'A' THEN
    RETURN SUBSTR(code, 1, 11) || 'B';
  ELSIF cell = 'C' THEN
    RETURN SUBSTR(code, 1, 11) || 'D';
  END IF;
  new_cell := CASE cell WHEN 'B' THEN 'A' WHEN 'D' THEN 'C' END;

  IF SUBSTR(unit_number, -1) != '0' THEN
    RETURN SUBSTR(code, 1, 8) || TO_CHAR(TO_NUMBER(unit_number)+1, 'FM009') || new_cell;
  END IF;
  new_unit_number := SUBSTR(unit_number, 1, 2) || '1';

  new_unit_letter := CASE unit_letter
                     WHEN 'A' THEN 'B'
                     WHEN 'B' THEN 'C'
                     WHEN 'C' THEN 'D'
                     WHEN 'D' THEN 'A'
                     WHEN 'E' THEN 'H'
                     WHEN 'F' THEN 'E'
                     WHEN 'G' THEN 'F'
                     WHEN 'H' THEN 'G'
                     WHEN 'I' THEN 'J'
                     WHEN 'J' THEN 'K'
                     WHEN 'K' THEN 'L'
                     WHEN 'L' THEN 'I'
                     WHEN 'M' THEN 'P'
                     WHEN 'N' THEN 'M'
                     WHEN 'O' THEN 'N'
                     WHEN 'P' THEN 'O'
                     END;
  IF unit_letter NOT IN ('D', 'E', 'L', 'M') THEN
     RETURN SUBSTR(code, 1, 6) || new_unit_letter || new_unit_number || new_cell;
  END IF;
  -- TODO: continue parsing the string right-to-left to parse the block, bcgs
  --       and nts_cell.
  RETURN NULL;
END left_code;
SELECT code,
       left_code(code) AS left
FROM   (SELECT '103G06H001B' AS code FROM DUAL);

Which outputs:

Note: Completing the function to handle shifting left in the nts_cell, bcgs and block elements and implementing similar functions for up, down and right is left to the reader - but you can follow the existing recipe and just extend the function.

fiddle

As an idea.
I will not try to apply PIVOT, UNPIVOT or any other means.
A somewhat verbose CROSS JOIN LATERAL - one row for one column.

Table_2x2

Table_10x10

select t10.n,t2.*
from table_10x10 t10
cross join lateral (
     select t2.n
       ,concat(t10.col1,t2.column1)col1C ,concat(t10.col1,t2.column2)col1D
       ,concat(t10.col2,t2.column1)col2C ,concat(t10.col2,t2.column2)col2D
       ,concat(t10.col3,t2.column1)col3C ,concat(t10.col3,t2.column2)col3D
       ,concat(t10.col4,t2.column1)col4C ,concat(t10.col4,t2.column2)col4D
       ,concat(t10.col5,t2.column1)col5C ,concat(t10.col5,t2.column2)col5D
       ,concat(t10.col6,t2.column1)col6C ,concat(t10.col6,t2.column2)col6D
       ,concat(t10.col7,t2.column1)col7C ,concat(t10.col7,t2.column2)col7D
       ,concat(t10.col8,t2.column1)col8C ,concat(t10.col8,t2.column2)col8D
       ,concat(t10.col9,t2.column1)col9C ,concat(t10.col9,t2.column2)col9D
       ,concat(t10.col10,t2.column1)col10C ,concat(t10.col10,t2.column2)col10D
     from table_2x2 t2)t2
order by t10.n,t2.n

fiddle (Oracle)
and PostgreSql

p.s. dual named table added for simplest change between Oracle and PostgreSql.