CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Shiv
Shiv

Reputation: 77

How to use case after where clause in sql server

category    Item    Price
    A       Pen     NULL
    B       Pen     10
    A     Pencil    10
    B     Pencil    8
    C     Pencil    7
    A     Note Book 40
    B     Note Book 30
    C     Note Book 20
    A     Bottle    NULL
    B     Bottle    80
    A       Ball    50
    B       Ball    40
    A       Bag     1000
    B       Bag     800

This is My data i want to Show only category A data if A price is null then show category B price. I was tried but don't know how i show the Data

select * from tbl1
where category = case when price is null then 'B' else 'A' end

When Run this query it's show only Category A data 

category    Item    Price
A         Pencil    10
A         Note Book 40
A         Ball      50
A         Bag       1000

Upvotes: 2

Views: 138

Answers (4)

Sri
Sri

Reputation: 155

I understand it as If an item in a category has a null price it should pick up that specific item from another category having a price

--Filter records having prices
with CTETable as
(
Select distinct category, item, price 
from tbl1
where price is not null
)
--distinct items
select * from
(select category, item, price, ROW_NUMBER() over (Partition by Item order by 
Category) as RowNo from CTETable)
as c
where c.RowNo=1

Upvotes: 0

user7715598
user7715598

Reputation: 

;WITH cte(category,Item,Price)
AS
(
 SELECT 'A','Pen'       ,NULL  UNION ALL
 SELECT 'B','Pen'       ,10    UNION ALL
 SELECT 'A','Pencil'    ,10    UNION ALL
 SELECT 'B','Pencil'    ,8     UNION ALL
 SELECT 'C','Pencil'    ,7     UNION ALL
 SELECT 'A','Note Book' ,40    UNION ALL
 SELECT 'B','Note Book' ,30    UNION ALL
 SELECT 'C','Note Book' ,20    UNION ALL
 SELECT 'A','Bottle'    ,NULL  UNION ALL
 SELECT 'B','Bottle'    ,80    UNION ALL
 SELECT 'A','Ball'      ,50    UNION ALL
 SELECT 'B','Ball'      ,40    UNION ALL
 SELECT 'A','Bag'       ,1000  UNION ALL
 SELECT 'B','Bag'       ,800
 )
 SELECT category,Item,Price From
 (
 SELECT *,ROW_NUMBER()Over(Partition by Price order by Price)seq From
 (
 SELECT o.*  FROM cte i 
 INNER JOIN cte o
 ON o.category=i.category
 WHERE o.category='A' AND o.Price!=i.Price
 )dt
) Final
where Final.seq=1

OutPut

category    Item    Price
A         Pencil    10
A         Note Book 40
A         Ball      50
A         Bag       1000

Output For value'B'

category    Item        Price
--------------------------------
B           Pencil      8
B           Pen         10
B           Note Book   30
B           Ball        40
B           Bottle      80
B           Bag         800

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

Using a left join and coalesce() (you could also use isnull()):

select a.Category, a.Item, coalesce(a.Price,b.Price) as Price
from yourtable a
  left join yourtable b
    on a.Item = b.Item
   and b.category = 'B'
where a.category = 'A'

coalesce() will return the first non null value from the parameters in order from left to right.

rextester demo: http://rextester.com/FZO89906

returns: 

+----------+-----------+-------+
| Category |   Item    | Price |
+----------+-----------+-------+
| A        | Pen       |    10 |
| A        | Pencil    |    10 |
| A        | Note Book |    40 |
| A        | Bottle    |    80 |
| A        | Ball      |    50 |
| A        | Bag       |  1000 |
+----------+-----------+-------+

Reference:

Upvotes: 0

Jake Ceballos
Jake Ceballos

Reputation: 241

Something like this

SELECT price
FROM table
WHERE
   category = CASE 
             WHEN price is not null THEN 'A'
             ELSE 'B'
            END

`

Upvotes: 1

Related Questions