2. Algorithm
  3. Blending 2 tables in SQL Oracle - Stack Overflow

Blending 2 tables in SQL Oracle - Stack Overflow

admin2025-04-19  0

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
Share edited Mar 4 at 16:00 Mike asked Mar 3 at 21:10 MikeMike 4,43517 gold badges64 silver badges87 bronze badges
Add a comment  | 

2 Answers 2

Reset to default 3

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:

NTS_CELL BCGS BLOCK UNIT_LETTER UNIT_NUMBER CELL_LETTER CODE
082 A 01 A 001 A 082B01A001A
083 A 01 A 001 A 083B01A001A
084 A 01 A 001 A 084B01A001A
085 A 01 A 001 A 085B01A001A
092 A 01 A 001 A 092B01A001A
093 A 01 A 001 A 093B01A001A
... ... ... ... ... ... ...

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:

CODE LEFT
103G06H001B 103G06H0002A

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

N COLUMN1 COLUMN2
1 C D
2 B A

Table_10x10

N COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
1 100 99 98 97 96 95 94 93 92 91
2 90 89 88 87 86 85 84 83 82 81
3 80 79 78 77 76 75 74 73 72 71
4 70 69 68 67 66 65 64 63 62 61
5 60 59 58 57 56 55 54 53 52 51
6 50 49 48 47 46 45 44 43 42 41
7 40 39 38 37 36 35 34 33 32 31
8 30 29 28 27 26 25 24 23 22 21
9 20 19 18 17 16 15 14 13 12 11
10 10 9 8 7 6 5 4 3 2 1 
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
N N COL1C COL1D COL2C COL2D COL3C COL3D COL4C COL4D COL5C COL5D COL6C COL6D COL7C COL7D COL8C COL8D COL9C COL9D COL10C COL10D
1 1 100C 100D 99C 99D 98C 98D 97C 97D 96C 96D 95C 95D 94C 94D 93C 93D 92C 92D 91C 91D
1 2 100B 100A 99B 99A 98B 98A 97B 97A 96B 96A 95B 95A 94B 94A 93B 93A 92B 92A 91B 91A
2 1 90C 90D 89C 89D 88C 88D 87C 87D 86C 86D 85C 85D 84C 84D 83C 83D 82C 82D 81C 81D
2 2 90B 90A 89B 89A 88B 88A 87B 87A 86B 86A 85B 85A 84B 84A 83B 83A 82B 82A 81B 81A
3 1 80C 80D 79C 79D 78C 78D 77C 77D 76C 76D 75C 75D 74C 74D 73C 73D 72C 72D 71C 71D
3 2 80B 80A 79B 79A 78B 78A 77B 77A 76B 76A 75B 75A 74B 74A 73B 73A 72B 72A 71B 71A
4 1 70C 70D 69C 69D 68C 68D 67C 67D 66C 66D 65C 65D 64C 64D 63C 63D 62C 62D 61C 61D
4 2 70B 70A 69B 69A 68B 68A 67B 67A 66B 66A 65B 65A 64B 64A 63B 63A 62B 62A 61B 61A
5 1 60C 60D 59C 59D 58C 58D 57C 57D 56C 56D 55C 55D 54C 54D 53C 53D 52C 52D 51C 51D
5 2 60B 60A 59B 59A 58B 58A 57B 57A 56B 56A 55B 55A 54B 54A 53B 53A 52B 52A 51B 51A
6 1 50C 50D 49C 49D 48C 48D 47C 47D 46C 46D 45C 45D 44C 44D 43C 43D 42C 42D 41C 41D
6 2 50B 50A 49B 49A 48B 48A 47B 47A 46B 46A 45B 45A 44B 44A 43B 43A 42B 42A 41B 41A
7 1 40C 40D 39C 39D 38C 38D 37C 37D 36C 36D 35C 35D 34C 34D 33C 33D 32C 32D 31C 31D
7 2 40B 40A 39B 39A 38B 38A 37B 37A 36B 36A 35B 35A 34B 34A 33B 33A 32B 32A 31B 31A
8 1 30C 30D 29C 29D 28C 28D 27C 27D 26C 26D 25C 25D 24C 24D 23C 23D 22C 22D 21C 21D
8 2 30B 30A 29B 29A 28B 28A 27B 27A 26B 26A 25B 25A 24B 24A 23B 23A 22B 22A 21B 21A
9 1 20C 20D 19C 19D 18C 18D 17C 17D 16C 16D 15C 15D 14C 14D 13C 13D 12C 12D 11C 11D
9 2 20B 20A 19B 19A 18B 18A 17B 17A 16B 16A 15B 15A 14B 14A 13B 13A 12B 12A 11B 11A
10 1 10C 10D 9C 9D 8C 8D 7C 7D 6C 6D 5C 5D 4C 4D 3C 3D 2C 2D 1C 1D
10 2 10B 10A 9B 9A 8B 8A 7B 7A 6B 6A 5B 5A 4B 4A 3B 3A 2B 2A 1B 1A

fiddle (Oracle)
and PostgreSql

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

转载请注明原文地址:http://conceptsofalgorithm.com/Algorithm/1745071791a283326.html

Blending 2 tables in SQL OracleStack Overflow

最新回复(0)