Re: How to write a crosstab which returns empty row results
On Sun, Dec 25, 2022 at 12:25 AM David Goldsmith 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, text, etc... ) ON usids.sid = ct.pop David J.
Re: How to write a crosstab which returns empty row results
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.johns...@gmail.com> wrote: > On Sun, Dec 25, 2022 at 12:25 AM David Goldsmith > 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, text, etc... ) ON > usids.sid = ct.pop > > David J. > >
Re: How to write a crosstab which returns empty row results
*That* covers this. On Sun, Dec 25, 2022 at 8:56 AM David Goldsmith wrote: > > > 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.johns...@gmail.com> wrote: > >> On Sun, Dec 25, 2022 at 12:25 AM David Goldsmith >> 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, text, etc... ) ON >> usids.sid = ct.pop >> >> David J. >> >>
