Thanks everyone, and thank you @Aaron Bertrand, your answer solved my problem :) !

i am struggling to find a solution to my problem, here is my query in SQL Server :

EDIT: a little more details : this is the kind of data that a have in my table :

my query gives this result :

so in the case when there is no records for a date, I need a query that returns :

instead of :

SELECT * FROM   
    (
      Select status from database.dbo.MyTable where date = '20220421' and status 
      in ('have a book','have a pdf')) y 
      PIVOT( Count (status) FOR y.status IN ( [have a book],[have a 
      pdf])
    ) pivot_table

This query works well but my issue is that i want to display 0 in the results if no row is found, i tried with IsNull, it works without the Pivot part, but i wasn't able to make it work with the Pivot.

Thanks in advance :)

CodePudding user response：

Since we're only dealing with bits and one or zero rows for a given date, you can just add a union to include a second row with zeros, and take the max (which will either pull the 0 or 1 from the real row, or the zeros from the dummy row when a real row doesn't exist).

SELECT [have a book] = MAX([have a book]),
       [have a pdf]  = MAX([have a pdf])
FROM
(
    SELECT [have a book], [have a pdf] FROM 
    (
      SELECT status FROM dbo.whatever 
       WHERE date = '20220421' 
         AND status IN ('have a book','have a pdf')
    ) AS src PIVOT
      ( 
         COUNT(status) FOR status IN 
         ([have a book],[have a pdf])
      ) AS pivot_table
    UNION ALL SELECT 0,0
) AS final;

• Example db<>fiddle