如何將一列中的值轉換為具有另一列值的列？

我有一個具有以下結構的數據庫：

**注意：**我事先不知道“類型”列中的值，因為它們是由使用者定義的。此外，可以有多個具有重疊日期、角色和類型的行。

我正在使用一個圖表庫，希望將數據分組如下：

到目前為止，我可以使用以下查詢對數據進行分組

select 
   role,
   type, 
   sum(duration) as total_minutes
from work
group by role, type;

如何“透視”/“轉置”數據，以便每一行代表一個角色，其中一列包含每種工作類型的分鐘總和？

實際上，我想轉置類似於 Pandas DataFrame.pivot_table函式的數據，但只使用 SQL。

首先，您需要使用該create extension tablefunc;命令安裝 tablefunc 擴展，否則樞軸功能crosstab將不起作用。

即使在閱讀了這個答案之後，還是建議您在此處閱讀 PostgreSQL on crosstab 的官方文件

至於如何做到這一點：

select *
from crosstab(
   'select
   role,
   type,
   sum(duration) as total_minutes
from work
group by role, type
order by type',
   'select distinct type from work order by type'
) as ct(
   role text,
   "Cleaning" text,
   "Food preparation" text
);

注意order by兩個查詢中的顯式子句，這是必須的，否則它可能會錯誤地映射值，因為沒有它，SQL 不能保證數據的順序。

您必須type在別名中指定列的每個可能輸出。

---

上述更動態的版本（儘管無論如何都不完美）：

create or replace function get_dynamic_transpose()
 returns text
 language plpgsql
as
$$
declare
   v_output_columns text;
begin
   select array_to_string(array_agg(distinct quote_ident(type) || ' ' || pg_typeof(type) || E' \n'),',','null')
   into v_output_columns
   from testing;

   return format(
'select *
from crosstab(
   ''select
   role,
   type,
   sum(duration) as total_minutes
from testing
group by role, type
order by type'',
   ''select distinct type from testing order by type''
) as ct(
   role text,
   %s
);', v_output_columns
   );
end;
$$;

此函式將返回您需要執行的查詢以獲得所需的結果。它將動態建構輸出所需的可能列的列表。這個函式絕對可以像這裡所做的那樣變得更通用，但是這樣做的工作量並不小，因為 PostgreSQL 不能返回一個它事先不知道它的定義的集合。

此函式還有另一個選項，而不是返回查詢字元串，它可以返回一個 json 對像數組，每個對象代表一行，您可以在應用程序端將此 json 拆分為正常的行和列。如果這樣的解決方案是可以接受的，那麼這很好：

create or replace function get_dynamic_transpose_jsonb()
 returns jsonb
 language plpgsql
as
$$
declare
   v_output_columns text;
   v_query text;
   v_result jsonb;
begin
   select array_to_string(array_agg(distinct quote_ident(type) || ' ' || pg_typeof(type) || E' \n'),',','null')
   into v_output_columns
   from testing;

   v_query = format(
'select jsonb_agg(ct)
from crosstab(
   ''select
   role,
   type,
   sum(duration) as total_minutes
from testing
group by role, type
order by type'',
   ''select distinct type from testing order by type''
) as ct(
   role text,
   %s
);', v_output_columns
   );

   execute v_query into v_result;

   return v_result;
end;
$$;

此函式的結果將類似於以下內容

[{"role": "Nurse", "Cleaning": "30", "Food preparation": null}, {"role": "Volunteer", "Cleaning": null, "Food preparation": "55"}]

引用自：https://dba.stackexchange.com/questions/311053