sql - Oracle return same alias for different cases on select

Question

What I am trying to do is based on a condition (value of a column) to return one or multiple columns.

I always need my query to return two columns: Article, Paragraph

If the value of a column list_value which I am checking is Article, then I only need to return the Article and null to Paragraph.

If the value of a column is Paragraph, I need to return both paragraph and article but the article's value now returns from a subquery based on the parent column value.

Table pro_table, Data:

SELECT
     tbl.parent,
     list.list_value,
     tbl.values_column,
     CASE
         WHEN list.list_value = 'Article' THEN tbl.values_column
     END AS article,
     CASE
         WHEN list.list_value = 'Paragraph' THEN tbl.values_column
     END AS paragraph,
     CASE
         WHEN list.list_value = 'Paragraph' THEN (
             SELECT
                 values_column
             FROM
                 pro_table
             WHERE
                 pro_table.id_pro_table = tbl.parent
         )
     END AS article
 FROM
     pro_table tbl 
     LEFT JOIN list ON list.id_list = tbl.id_t_list

Answer 1

Assuming your subquery already gets the correct value back, and you're just asking how to get both 'article' values in the same column, all you need to do is move the subquery into a second when check within the first case expression, instead of as its own column:

SELECT
     tbl.parent,
     list.list_value,
     tbl.values_column,
     CASE
         WHEN list.list_value = 'Article' THEN tbl.values_column
         WHEN list.list_value = 'Paragraph' THEN (
             SELECT
                 values_column
             FROM
                 pro_table
             WHERE
                 pro_table.id_pro_table = tbl.parent
         )
     END AS article,
     CASE
         WHEN list.list_value = 'Paragraph' THEN tbl.values_column
     END AS paragraph
 FROM
     pro_table tbl 
     LEFT JOIN list ON list.id_list = tbl.id_t_list

You could replace the subquery with a left join back to the same table and coalesce the results:

SELECT
     tbl.parent,
     list.list_value,
     tbl.values_column,
     COALESCE (
         CASE
             WHEN list.list_value = 'Article' THEN tbl.values_column
         END,
         tbl2.values_column
     ) AS article,
     CASE
         WHEN list.list_value = 'Paragraph' THEN tbl.values_column
     END AS paragraph
 FROM
     pro_table tbl 
     LEFT JOIN list ON list.id_list = tbl.id_t_list
     LEFT JOIN pro_table tbl2 ON tbl2.id_pro_table = tbl.parent

... but that's making some assumptions about the hierarchy, so you probably need to tighten the join up based on the list type.

db<>fiddle with invented relationships between the same six rows you showed, as we don't have the actual IDs.