From: | David Goldsmith <d(dot)l(dot)goldsmith(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to write a crosstab which returns empty row results |
Date: | 2022-12-25 16:57:47 |
Message-ID: | CAFtPsZrQ+FYEKV0D6H9928cq0Khfwh3UkzRae7=E-J9X5LSU2w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
*That* covers this.
On Sun, Dec 25, 2022 at 8:56 AM David Goldsmith <d(dot)l(dot)goldsmith(at)gmail(dot)com>
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(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> 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.
>>
>>
From | Date | Subject | |
---|---|---|---|
Next Message | qihua wu | 2022-12-26 09:29:11 | best practice to patch a postgresql version? |
Previous Message | David Goldsmith | 2022-12-25 16:56:37 | Re: How to write a crosstab which returns empty row results |