CODEWITHSUNDEEP
sqloracle-database
wilbi
wilbi

Reputation: 235

Oracle SQL: Using same alias In CASE statement to select in one case one column, in the other two columns and another three with the same alias

I am doing something wrong in a select with cases in Oracle and I'll appreciate the help from the stackoverflow community.

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 three columns: Article, Paragraph, Sub_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 both Paragraph and Sub Paragraph. If the value of a column is Paragraph, I need to return both paragraph and article and the Sub_Paragraph should be null. If the value of a column is Sub_Paragraph, I need to return all values for article, paragraph and Sub_Paragraph.

SELECT

     CASE
         WHEN att.list_value = 'Sub_Paragraph' THEN att.column_with_subpar_value
     END AS sub_paragraph,
     CASE
         WHEN att.list_value = 'Sub_Paragraph' THEN att.column_with_par_value
     END AS paragraph,
     CASE
         WHEN att.list_value = 'Sub_Paragraph' THEN att.column_with_article_value
     END AS article,
   
     CASE
         WHEN att.list_value = 'Paragraph' THEN att.column_with_par_value
     END AS paragraph,
     CASE
         WHEN att.list_value = 'Paragraph' THEN att.column_with_article_value
     END AS article,
  
     CASE
         WHEN att.list_value = 'Article' THEN att.column_with_article_value
     END AS article
  
 FROM
     att
     
 WHERE
     id = 1

but with this approach I am getting column like article_1 , article_2 etc. What's the best way to succeed it?

Upvotes: 1

Views: 203

Answers (2)

Jim Macaulay
Jim Macaulay

Reputation: 5155

Looking at your requirement, hope below is what you are expecting.

SELECT CASE
         WHEN att.list_value = 'Sub_Paragraph' THEN
          att.column_with_subpar_value
       END AS sub_paragraph,
       CASE
         WHEN att.list_value in ('Sub_Paragraph', 'Paragraph') THEN
          att.column_with_par_value
       END AS paragraph,
       CASE
         WHEN att.list_value in ('Sub_Paragraph', 'Paragraph', 'Article') THEN
          att.column_with_article_value
       END AS article
  FROM att
 WHERE id = 1;

Upvotes: 4

bash
bash

Reputation: 80

One of the approaches on how this can be resolved would be to concatenate all the columns into one column with a known separator, which can be split later into the desired set of columns.

Upvotes: 0

Related Questions