Query that shows all rows but not all data in all rows-CodePudding

Imagine this database:

CustomerID	ItemID	EventType
ABC	123	In Stock
ABC	456	On Order
DEF	134	Disc
DEF	789	On Order

I want to run a report that will show me all of these records, but will only show me the status if the item is On Order.

So the output looks like this:

CustomerID	ItemID	EventType
ABC	123
ABC	456	On Order
DEF	134
DEF	789	On Order

Is there a way to do this?

I found this answer: SQL:Show column only if has data in it and tried this:

if ((select EventType from InventoryEvents) = 'On Order')
    select ItemID, CustomerID, EventType from InventoryEvents
else
    select ItemID, CustomerID from InventoryEvents

And got this error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I'm very close to putting a conditional format on the output in Excel that writes the data I don't want in white. But of course, I'm designing a report for my boss and want it work-proof on their end.

CodePudding user response：

You want a case expression to conditionally show the EventType.

select ItemID, CustomerID
  , case when EventType = 'On Order' then EventType else '' end EventType
from InventoryEvents;

CodePudding user response：

Sounds like you want a CASE expression:

SELECT  CustomerID, 
        ItemID, 
        CASE WHEN EventType <> 'On Order' THEN '' ELSE EventType END AS EventType 
FROM    InventoryEvents;