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

On Sun, Dec 25, 2022 at 12:25 AM David Goldsmith <d(dot)l(dot)goldsmith(at)gmail(dot)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.pop

David J.