How can i achieve the following in oracle?

Question

I'm trying to convert below table to the desired output like below. how can i achieve the below desired output table

Data Set:

question_id    element_id
1              7
1              8
1              z
2              x
2              9
2              10
3              10
3              11
3              12
3              y

Desired Result:

question_id    element_id       element
1              7                   z
1              8                   z
2              9                   x
2              10                  x
3              10                  y
3              11                  y
3              12                  y

OR

question_id    element_id       element
1              7                   z
1              8                   null
2              9                   x
2              10                  null
3              10                  y
3              11                  null
3              12                  null

Answer 1

You could use conditional aggregation to find the non digit value for each question_id group, with the help of analytic functions:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY question_id
                                   ORDER BY element_id) rn
    FROM yourTable t
),
cte2 AS (
    SELECT
        question_id,
        element_id,
        CASE WHEN rn = 1
             THEN MAX(CASE WHEN REGEXP_LIKE(element_id, '^[A-Za-z]+$')
                           THEN element_id END)
                  OVER (PARTITION BY question_id) END AS element
    FROM cte
)

SELECT
    question_id,
    element_id,
    element
FROM cte2
WHERE
    REGEXP_LIKE(element_id, '^[0-9]+$')
ORDER BY
    question_id,
    element_id;

Demo