Oracle SELECT every distinct column over every another distinct column-CodePudding

I have the following table:

CREATE TABLE TEMP_TEST 
(
    CODE NUMBER,
    DATE_COL NUMBER,
    VAL NUMBER
);

With values:

INSERT INTO TEMP_TEST (code, DATE_COL, val) 
VALUES (1, 20210101, 1);
INSERT INTO TEMP_TEST (code, DATE_COL, val) 
VALUES (1, 20210102, 1);
INSERT INTO TEMP_TEST (code, DATE_COL, val) 
VALUES (1, 20210103, 1);
INSERT INTO TEMP_TEST (code, DATE_COL, val) 
VALUES (2, 20210101, 1);

As a result, I would like to get every distinct DATE_COL row over every distinct CODE row:

CODE|DATE_COL|VAL|
---- -------- --- 
   1|20210101|  1|
   1|20210102|  1|
   1|20210103|  1|
   2|20210101|  1|
   2|20210102|  1| (new line)
   2|20210103|  1| (new line)

This is the distinct of every dates:

SELECT DATE_COL 
FROM TEMP_TEST 
GROUP BY DATE_COL

This is the distinct of every codes:

SELECT code 
FROM TEMP_TEST 
GROUP BY CODE

How do I get every dates over every codes, so that the result set has the multiplied size of the two above distinct result sets?

CodePudding user response：

You can use a PARTITIONed OUTER JOIN:

SELECT t.code,
       d.date_col,
       COALESCE(t.val, 1) AS val
FROM   (
         SELECT DISTINCT date_col FROM temp_test
       ) d
       LEFT OUTER JOIN temp_test t
       PARTITION BY (t.code)
       ON (t.date_col = d.date_col);

Which, for your sample data, outputs:

CODE DATE_COL VAL

1 20210101 1

1 20210102 1

1 20210103 1

2 20210101 1

2 20210102 1

2 20210103 1

CODE	DATE_COL	VAL
1	20210101	1
1	20210102	1
1	20210103	1
2	20210101	1
2	20210102	1
2	20210103	1

db<>fiddle here

Note: this also works when the VAL values are not identical as the correlation is kept between code, date_col and val. If you naively use CROSS JOIN then the correlation is not maintained and extra rows are likely to be generated. db<>fiddle

CodePudding user response：

A simple option is to perform triple self cross join:

SQL> select distinct a.code, b.date_col, c.val
  2  from temp_test a cross join temp_test b cross join temp_test c
  3  order by 1, 2, 3;

      CODE   DATE_COL        VAL
---------- ---------- ----------
         1   20210101          1
         1   20210102          1
         1   20210103          1
         2   20210101          1
         2   20210102          1
         2   20210103          1

6 rows selected.

SQL>

How it'll behave with huge tables, that's another question. If they aren't that huge, I guess it should be OK.