Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 <b55white@xxxxxxxxx> 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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux