How to write a crosstab which returns empty row results

From: David Goldsmith <d(dot)l(dot)goldsmith(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: How to write a crosstab which returns empty row results
Date: 2022-12-25 03:03:13
Message-ID: CAFtPsZqfiRvRp1EngwEfyNMu=nW11C0i7WvtYtBpO0gjjwLPwQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi. New subscriber and intermediate level SQL writer here, still pretty new
to Postgresql (but I don't know how to do the following in TSQL either).

I've figured out how to write a crosstab query I need; the problem is that
the number of row results should be 72, but I'm only getting 41. I'm
pretty sure this is because there actually isn't any data matching my where
constraints for the 31 missing row header values, but I nevertheless need
them in my result, with NULLs where there is no data. How do I force
"empty rows" to be included in my query output? (I've tried LEFT JOINing to
the row header results, and using CASE statements; but due to my
unfamiliarity w/ using crosstab, I'm not sure if I've used those correctly
in the current context; so if using either or both of those is part of the
solution, please do more than simply saying "use a Left join" or "use a
case statement," i.e., furnish an example, please.

Thanks!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brad White 2022-12-25 03:24:59 Re: How to write a crosstab which returns empty row results
Previous Message Brad White 2022-12-23 16:32:46 Re: Implementing foreign data wrappers and avoiding n+1 querying