postgresql行轉列與列轉行圖文教程

更新時間：2023年06月10日 11:35:09 作者：Moshow鄭鍇

PostgreSQL是一種開源的關系型數(shù)據(jù)庫,它提供了多種管理工具來操作數(shù)據(jù)庫,下面這篇文章主要給大家介紹了關于postgresql行轉列與列轉行的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下

列轉行

postgresql列轉行的思路主要是利用string_to_array進行數(shù)組轉換，然后用unnest進行行拆分

select t.bid_unit,unit_id from unit t
where t.unit_id=1947;
result=> 中國信息通信研究院;北京市海淀區(qū)學院
-- by zhengkai.blog.csdn.net

select unnest(string_to_array(t.bid_unit,';')),unit_id from unit t
where t.unit_id=1947;
result=> 
中國信息通信研究院
北京市海淀區(qū)學院
-- by zhengkai.blog.csdn.net

pgsql官方對functions-array的解釋

Function	Return Type	Description	Example	Result
string_to_array(text, text [, text])	text[]	splits string into array elements using supplied delimiter and optional null string (使用提供的分隔符和可選的空字符串將字符串分割為數(shù)組元素)	string_to_array(‘xx_^yy_^zz’, ‘_^’, ‘yy’)	{xx,NULL,zz}
unnest(anyarray)	setof anyelement	expand an array to a set of rows(將數(shù)組展開到一組行)	unnest(ARRAY[1,2])	1 2 (2 rows)

行轉列

用postgresql的crosstab交叉函數(shù)

-- by zhengkai.blog.csdn.net
create table sales(year int, month int, qty int);
insert into sales values(2022, 1, 1000);
insert into sales values(2022, 2, 1500);
insert into sales values(2022, 7, 500);
insert into sales values(2022, 11, 1500);
insert into sales values(2022, 12, 2000);
insert into sales values(2023, 1, 1200);

select * from crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) as (
  year int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int,
  "Dec" int
);
 year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2022 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2023 | 1200 |      |     |     |     |     |     |     |     |     |      |
(2 rows)

可以參考pgsql官方的tablefunc實用說明

Function	Returns	Description
normal_rand(int numvals, float8 mean, float8 stddev)	setof float8	Produces a set of normally distributed random values（產生一組正態(tài)分布的隨機值）
crosstab(text sql)	setof record	Produces a “pivot table” containing row names plus N value columns, where N is determined by the row type specified in the calling query（生成一個包含行名和N個值列的“數(shù)據(jù)透視表”，其中N個由調用查詢中指定的行類型決定）
crosstabN(text sql)	setof table_crosstab_N	Produces a “pivot table” containing row names plus N value columns. crosstab2, crosstab3, and crosstab4 are predefined, but you can create additional crosstabN functions as described below（生成一個包含行名和N個值列的“數(shù)據(jù)透視表”。交叉表2、交叉表3和交叉表4都是預定義的，但是您可以創(chuàng)建額外的跨表n函數(shù)，如下面所述）
crosstab(text source_sql, text category_sql)	setof record	Produces a “pivot table” with the value columns specified by a second query（生成具有由第二個查詢指定的值列的“數(shù)據(jù)透視表”）
crosstab(text sql, int N)	setof record	Obsolete version of crosstab(text). The parameter N is now ignored, since the number of value columns is always determined by the calling query（過時版本的交叉表（文本）。參數(shù)N現(xiàn)在被忽略，因為值列的數(shù)量總是由調用查詢決定）
connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ])	setof record	Produces a representation of a hierarchical tree structure（生成層次樹結構的表示）