Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p9HdM-0003e7-O0 for pgsql-general@arkaria.postgresql.org; Sun, 25 Dec 2022 03:25:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p9HdL-00006W-6r for pgsql-general@arkaria.postgresql.org; Sun, 25 Dec 2022 03:25:07 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p9HdK-00006J-QH for pgsql-general@lists.postgresql.org; Sun, 25 Dec 2022 03:25:06 +0000 Received: from mail-il1-x12b.google.com ([2607:f8b0:4864:20::12b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p9HdH-00066R-68 for pgsql-general@lists.postgresql.org; Sun, 25 Dec 2022 03:25:06 +0000 Received: by mail-il1-x12b.google.com with SMTP id o8so4289607ilq.6 for ; Sat, 24 Dec 2022 19:25:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=gWsqsV5guuueYr4z6NxJ15KJS4BY7BCQAGSvt6GsO5E=; b=DuNyIev0T/MOcrJaaH2QZsWokKkKjejAPAHbf4AYAyLTo4mz0xGaWhVXXsfnMcIRnH Yhuagj8WEiKwFz0zPept9qL7FnMYys1r66DJ3UUNeTQYXFEz/lVLUzdiDUgj0Qty5lnJ kTHB6lVmFfD46x24cXJiGfSjPkOvPJibYLtDihbzq5Fn2JgD8CWyXxsvs8L3mD1Wx1ry /2K6hUosGEV98uyw0qpYkVfJd+wQvKBEErF78rCbCnsDh1S6lP2L7Qlhtn3XexBj4rm3 A/0lmEIo6vZlg1MaiIfrdJQc0NcPJnL8GweEZD3BrOcf4X2Dyy1Pzt0tc1lTFZVJy7IZ c6Xw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=gWsqsV5guuueYr4z6NxJ15KJS4BY7BCQAGSvt6GsO5E=; b=K2o5YFZ0Z9th2/y0HoG179SUeOvAeOvfeXyX8RhLBb0UaN+QURZCSsL4mhPZYRTuXd mRxI2MPRE0rMmtGwFlOtoOI0SJJCfpNkOm1QF+UlkI6brVhdDMQ7jRTkAonSO6lMGpEH E1gn8Axol4bjorJ4J/vT6WWarHnBF8H+rPQED1fGm1FyTsWC720Cof8pSpc4Xk6WO/bI DMCOksxHfRqa/SH/eaTM1c+IlfhqDoZ47blIy/GOx4QcvuJ+Cs9wpM9CFbxdzB3hFvhH INZNvpuCTjJLd9Oj2kSWusHQjsgHDEkpkBvqhv8pThYHvbJVrRMVe6HqSONQ2WDCRkTa DGoA== X-Gm-Message-State: AFqh2ko71Isb1Bf0xa4uTo/+FW1AYLpZ318fQ9t/9OSK15n0f7BSmquS EBRr1dHZU/5xB2ubbyzedgNvVk7pbBc= X-Google-Smtp-Source: AMrXdXuwjFj+lb3vtYzx5gz4IvUlUGV4abx9+I9Cn7rg/UZ8N7xPdZcaqQz8S5eQdypC3eWscGZhcg== X-Received: by 2002:a05:6e02:c68:b0:30b:f61f:4bcd with SMTP id f8-20020a056e020c6800b0030bf61f4bcdmr2918984ilj.9.1671938701451; Sat, 24 Dec 2022 19:25:01 -0800 (PST) Received: from [192.168.1.115] (h244.184.30.69.dynamic.ip.windstream.net. [69.30.184.244]) by smtp.gmail.com with ESMTPSA id y19-20020a056e020f5300b00302fb4c14absm736744ilj.66.2022.12.24.19.25.00 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 24 Dec 2022 19:25:01 -0800 (PST) Message-ID: Date: Sat, 24 Dec 2022 21:24:59 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101 Thunderbird/102.0.2 Subject: Re: How to write a crosstab which returns empty row results Content-Language: en-US To: David Goldsmith , pgsql-general@lists.postgresql.org References: From: Brad White In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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.