Using Single Case Statement in Select Query to show multiple columns

[govardan_raj]

Hi Team,

I have a requirement where i have to select Two Columns using the Case Statement in Query.

For now am using the below query and am getting the output

SELECT CUSTOMER,CREATE_DATE,
CASE  
WHEN  SCHEME_A IN ('Y') THEN 'SCHEME_A'
WHEN  SCHEME_B IN ('Y') AND   SCHEME_A IN ('N') THEN 'SCHEME_B'
ELSE 
NULL
END
AS WARRANTY_SCHEME,
SCHEME_A_VALUE,SCHEME_B_VALUE  
FROM PRICE_SCHEME_TABLE WHERE CUSTOMER = '*******' AND CREATE_DATE > '2020.01.01' 

am getting the output as shown below

but i want the output to be further reduced to three columns as shown below

where using the same case statement in above query , we have to select the corresonding scheme & the corresponding scheme values , how to modify the query to above this result.

Regards

Govardan

[former_member358098]

if the unnecessary value is always 0, you can go for sum(SCHEME_A_VALUE,SCHEME_B_VALUE)

[lbreddemann]

artemt's tip is not wrong, but using SUM() will require a GROUP BY clause in the SELECT statement.

The same idea could be implemented without GROUP BY when using the expression (SCHEMA_A_VALUE + SCHEME_B_VALUE).

Which option one is most appropriate is not clear cut - the CASE option provides a nice symmetry to the WARRANTY_SCHEME column which might be worth the slightly longer code.

[lbreddemann · Accepted solution]

I think your description of the result set you're getting is incorrect.

The SQL statement projects the following columns:

SELECT 
    CUSTOMER
  , CREATE_DATE
  , CASE ... AS  WARRANTY_SCHEME
  , SCHEME_A_VALUE
  , SCHEME_B_VALUE  
FROM

So, your output is missing the CREATE_DATE column.

If you want the output to only include CUSTOMER, WARRANTY_SCHEME and WARRANTY_VALUE you will have to create a WARRANTY_VALUE column and remove CREATE_DATE, SCHEMA_A_VALUE, and SCHEMA_B_VALUE from the projection list:

SELECT 
    CUSTOMER
  , CASE ... AS WARRANTY_SCHEME
  , CASE ... AS WARRANTY_VALUE 
FROM

Since the logic for the CASE statement that computes WARRANTY_VALUE is the same as for WARRANTY_SCHEME I leave the implementation for you.

[dhanudjro · Accepted solution]

Hi ,

Instead of fix values, say if i have one of the fields in the table to displayed in the other field, is it possbile to do it with CASE? In below example i need to join mara and makt if language is E, display that text in maktx_e, if 1 then maktx_1.

EX :

MATNR SPRAS MAKTX_E MAKTX_1

1 E and 1 TEXT_E TEXT_1