CODEWITHSUNDEEP
sqlsql-server
thomsan
thomsan

Reputation: 483

How to check column value inside case statement

I have following table structure,

ID  TypeID  No
1   10  50
2   20  100

I need to get the No column value by checking TypeID column value, I did as follows,

  select case when ( TypeID = 10) THEN  NO END AS NO1,
  CASE when (TypeID = 20 ) THEN  NO END AS NO2  from SAMPLE_TABLE

But its return NULL,

Expected Output is

NO1 NO2
1  0

Upvotes: 0

Views: 1306

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You can use conditional aggregation :

with sample_table(ID,TypeID,No) as
(
 select 1,   10,  50   union all
 select 2,   20,  100
)
select max(case when TypeID = 10 THEN  NO END) AS NO1,
       max(case when TypeID = 20 THEN  NO END) AS NO2  
 from sample_table;

NO1  NO2
---  ---
 50  100

Edit(depending on your comment) : If No column being of bit type, then consider :

select max(case when TypeID = 10 THEN  NO+0 END) AS NO1,
       max(case when TypeID = 20 THEN  NO+0 END) AS NO2  
 from sample_table;

NO1  NO2
---  ---
1    0

Demo

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521179

A general approach which would be flexible here would be to use ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY TypeID) rn
    FROM yourTable
)

SELECT
    MAX(CASE WHEN rn = 1 THEN No END) AS NO1,
    MAX(CASE WHEN rn = 2 THEN No END) AS NO2
FROM cte;

Upvotes: 0

Related Questions