Обсуждение: How to write a crosstab which returns empty row results
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.
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 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.
(''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''
)
On Sun, Dec 25, 2022 at 12:25 AM David Goldsmith <d.l.goldsmith@gmail.com> wrote:(''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''
)
This is basically your issue - specifying the items you want as individual items in an IN construct instead of making them into a set (in this case an array so the set is compactified into a single value):Something like:WITH sids (sid_array) AS (SELECT ARRAY['.........','........'.etc...])SELECT usids.sid, ct.*FROM (SELECT unnest(sid_array) AS sid FROM sids) AS usids (sid)LEFT JOIN crosstab( format($$SELECT ... s.s_id = ANY(%L) ...$$, (SELECT sids.sid_array::text FROM sids) ) AS ct ( pop text, YYYY text, etc... ) ON usids.sid = ct.popDavid J.
Thanks, David. There's a lot of stuff i'm not familiar with in there: I don't suppose you know of a tutorial they covers this?On Sun, Dec 25, 2022 at 8:46 AM David G. Johnston <david.g.johnston@gmail.com> wrote:On Sun, Dec 25, 2022 at 12:25 AM David Goldsmith <d.l.goldsmith@gmail.com> wrote:(''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''
)
This is basically your issue - specifying the items you want as individual items in an IN construct instead of making them into a set (in this case an array so the set is compactified into a single value):Something like:WITH sids (sid_array) AS (SELECT ARRAY['.........','........'.etc...])SELECT usids.sid, ct.*FROM (SELECT unnest(sid_array) AS sid FROM sids) AS usids (sid)LEFT JOIN crosstab( format($$SELECT ... s.s_id = ANY(%L) ...$$, (SELECT sids.sid_array::text FROM sids) ) AS ct ( pop text, YYYY text, etc... ) ON usids.sid = ct.popDavid J.