How to write a crosstab which returns empty row results

[David Goldsmith]

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!

Re: How to write a crosstab which returns empty row results

[Brad White]

On 12/24/2022 9:03 PM, David Goldsmith wrote:
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;


Can you give us a head start by showing the query you have now that is 
not working.


Extra points if you give simple create/populate statements that 
demonstrate the problem.


Hope that helps,
Brad.

Re: How to write a crosstab which returns empty row results

[David Goldsmith]

Here you go:

SELECT *

FROM crosstab(

'SELECT s.s_n AS Pop

, ad.a_d_y::text AS Yr

, ad.s_a_qty::text --for some Pop all of
these are null for every Yr



FROM st AS s

JOIN s_d_s AS sds ON s.s_id = sds.s_id

JOIN a_d_d AS ad ON sds.sds_id = ad.sds_id

JOIN d_t_l AS dtl ON dtl.dtl_id = sds.dtl_id



WHERE dtl.dtl_id =
''3edcb910-fc0c-49e0-be93-a93e98cb12bb''

  AND s.s_id IN (


''9adfe0ee-af21-4ec7-a466-c89bbfa0f750''

  ,
''8714b2e3-d7ba-4494-a3ed-99c6d3aa2a9c''

  ,
''45ecb932-ece9-43ce-8095-54181f33419e''

  ,
''fa934121-67ed-4d10-84b0-c8f36a52544b''

  ,
''b7d5e226-e036-43c2-bd27-d9ae06a87541''

)

ORDER BY 1,2',

'SELECT DISTINCT a_d_y FROM a_d_d WHERE a_d_y BETWEEN 2017
AND 2021 ORDER BY 1')

AS final_result(Pop TEXT,

"2017" TEXT,

"2018" TEXT,

"2019" TEXT,

"2020" TEXT,

"2021" TEXT

   );

A row for each one of the matching s.s_id values should be displayed, even
if all the ad.s_a_qty values for that Yr are NULL; right now, the query
works, but it only returns matching rows for which at least one year has a
non-NULL ad.s_a_qty.

Thanks in advance for your help.

On Sat, Dec 24, 2022 at 7:25 PM Brad White  wrote:

> On 12/24/2022 9:03 PM, David Goldsmith wrote:
> > 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;
> >
> Can you give us a head start by showing the query you have now that is
> not working.
>
> Extra points if you give simple create/populate statements that
> demonstrate the problem.
>
> Hope that helps,
> Brad.
>
>