CODEWITHSUNDEEP
sqlsql-servert-sqlfilterpivot
Sabi Tech
Sabi Tech

Reputation: 81

Pivot table how to not allow nulls in multiple columns

I want to put a condition if [2015] and [2016] are NULL then hide row, but I cant pull it off.

I don't have interest in the billname if it doesn't have data.

SELECT 
    IssuedByUserGroup,
    BILLNAME, 
    SUM([2016]) AS [2016],
    SUM([2015])AS [2015]
FROM Sum_Orders 
PIVOT
(
    SUM(Sum_SellPrice)
        FOR OrderperiodYear IN ([2016],[2015]) 
) AS pvt
WHERE  ( (MONTH(OrderDate) = MONTH(GETDATE()) 
    AND day(OrderDate) <= DAY(GETDATE())) OR MONTH(OrderDate) < MONTH(GETDATE())) 
--This part above is just for getting the data since january 1 to sep 23(to the date)
    AND OrderStatus in ('Complete','invoiced') 
    AND OrderPrefix IN ('LAX6') 
GROUP BY BILLNAME,IssuedByUserGroup
ORDER BY IssuedByUserGroup desc

This is returning the following as expected:

enter image description here

Thank you !

Upvotes: 1

Views: 1041

Answers (2)

gofr1
gofr1

Reputation: 15997

Add COALESCE under GROUP BY part:

HAVING COALESCE(SUM([2016]), SUM([2015])) IS NOT NULL

Upvotes: 2

paulbarbin
paulbarbin

Reputation: 382

You should be able to use a WHERE clause below the PIVOT, something like this:

WHERE [2015] IS NOT NULL

Upvotes: 0

Related Questions