使用一列中的值作為列名並彙總另一表中的數據

March 12, 2022

我將盡力描述這一點。所以我有3個表如下：
項目
公制
project_metric
我正在嘗試做的是編寫一個將產生以下結果的查詢。
我不會粘貼我的任何嘗試，因為它們很可悲。
幾點注意事項：
此數據庫上的 PostgreSQL 版本為 9.5。
**（重要）**此查詢將在一個長查詢中實現，該查詢顯示有關項目的大約 65 列資訊，但為簡潔起見，顯然我省略了一些項目屬性。我將發布目前查詢的部分片段。
SELECT DISTINCT
   p.title AS "Project Title", d.name AS "Center/Department",

  // This is a query that currently exists in the long query I mentioned.
   (SELECT DISTINCT string_agg(DISTINCT re.name, ', ') AS "Research Exchange"
    FROM research_exchange re
        JOIN project_research_exchange pre ON pre.research_exchange_id = re.id
    WHERE pre.project_id = p.id)

FROM project p
   LEFT JOIN department d ON p.department_id = d.id
GROUP BY p.title
ORDER BY p.title

對於許多結果列，我建議使用交叉表查詢。如果您不熟悉，請先閱讀以下內容：
PostgreSQL 交叉表查詢
您的測試案例似乎具有誤導性，因為它不需要聚合。假設實際上每個項目和指標有多行，您需要先匯總總和。
SELECT p.title AS project_title, ct.*
FROM   crosstab(
 'SELECT project_id, metric_id, sum(value) AS sum_val
  FROM   project_metric
  GROUP  BY 1, 2
  ORDER  BY 1, 2'
, $$SELECT unnest('{101, 102}'::int[])$$    -- more ... ①, ③
  ) AS ct(project_id int
        , "Number of exhibits" bigint
        , "Number of stakeholders" bigint)  -- more ... ②
JOIN   project p ON p.id = ct.project_id;
db<>在這裡擺弄
在加入之前匯總和交叉製表核心表。這便宜得多。
metric更重要的是，如果我們直接在函式呼叫中轉換metric_id為name輸出列，則根本不需要加入表。crosstab()只需確保第二個函式參數①中的數組與列定義列表②保持同步即可。
如果您有許多結果列（“資訊列”），或者它們的數量正在動態變化，請考慮動態建構語句。看：
如何在結果表定義未知的情況下生成旋轉的 CROSS JOIN？
PostgreSQL 將列轉換為行？轉置？
關於美元報價③：
在 PostgreSQL 中插入帶單引號的文本