Q:

Oracle SQL check if two values exists across a partition

Trying to check if data_type 'G' and 'J' exists for a given id, if true display 'Both', else output data_type. Help with the SQL statement would be appreciated.
SELECT 
        data_type,
        id,
        max(case 
        when data_type= 'G' and data_type= 'J' then 'Both'
        else data_type
        end) over (partition by id order by id) as
         condition
    FROM my_table;

desired output
+-----------+-----+-----------+
| data_type | id  | condition |
+-----------+-----+-----------+
| J         |  A  |  Both     |
| J         |  A  |  Both     |
| G         |  A  |  Both     |
| J         |  A  |  Both     |
| J         |  B  |  J        |
| J         |  B  |  J        |
| J         |  B  |  J        |
+-----------+-----+-----------+

A:

Use a DECODE with a COUNT(DISTINCT...) window function.  I.e.,
SELECT t.*, 
       DECODE(count(distinct data_type) over ( partition by id), 
         1, data_type,
         2, 'Both'
         ) condition
FROM test_data t;

Example
with test_data ( data_type, id ) AS (
SELECT 'J','A' FROM DUAL UNION ALL
SELECT 'J','A' FROM DUAL UNION ALL
SELECT 'G','A' FROM DUAL UNION ALL
SELECT 'J','A' FROM DUAL UNION ALL
SELECT 'J','B' FROM DUAL UNION ALL
SELECT 'J','B' FROM DUAL UNION ALL
SELECT 'J','B' FROM DUAL)
SELECT t.*, DECODE(count(distinct data_type) over ( partition by id), 1, data_type, 2, 'Both') condition
FROM test_data t;

+-----------+----+-----------+
| DATA_TYPE | ID | CONDITION |
+-----------+----+-----------+
| G         | A  | Both      |
| J         | A  | Both      |
| J         | A  | Both      |
| J         | A  | Both      |
| J         | B  | J         |
| J         | B  | J         |
| J         | B  | J         |
+-----------+----+-----------+

