CODEWITHSUNDEEP
sqloracle-database
Gábor DANI
Gábor DANI

Reputation: 2135

Oracle SELECT every distinct column over every another distinct column

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?

Upvotes: 0

Views: 67

Answers (3)

Michael
Michael

Reputation: 3256

You can perform cartesian join between the distinct values of DATE_COL and CODE. It is unclear what do you want to show from the VAL column, so I do not display it

select date_col_data.DATE_COL, code_data.code
from
(SELECT distinct DATE_COL 
FROM TEMP_TEST ) date_col_data,
( SELECT distinct code 
FROM TEMP_TEST ) code_data

db fiddle here https://dbfiddle.uk/?rdbms=oracle_18&fiddle=a9cf8afb7b5e1d652ddb5362066f0970

Upvotes: 0

MT0
MT0

Reputation: 167981

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

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

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.

Upvotes: 0

Related Questions