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]

 



*That* covers this.

On Sun, Dec 25, 2022 at 8:56 AM David Goldsmith <d.l.goldsmith@xxxxxxxxx> 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.johnston@xxxxxxxxx> wrote:
On Sun, Dec 25, 2022 at 12:25 AM David Goldsmith <d.l.goldsmith@xxxxxxxxx> 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.


[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