列转行
PostgreSQL列转行的思路主要是利用string_to_array进行数组转换，然后用unnest进行行拆分
select t.bid_unit,unit_id from unit t
where t.unit_id=1947;
result=> 中国信息通信研究院;北京市海淀区学院
-- by zhengkai.blog.csdn.net
select unnest(string_to_array(t.bid_unit,';')),unit_id from unit t
where t.unit_id=1947;
result=>
中国信息通信研究院
北京市海淀区学院
-- by zhengkai.blog.csdn.netpgsql官方对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 (使用提供的分隔符和可选的空字符串将字符串分割为数组元素) | string_to_array(‘xx^yy^zz’, ‘^’, ‘yy’) | {xx,NULL,zz} |
| unnest(anyarray) | setof anyelement | expand an array to a set of rows(将数组展开到一组行) | unnest(ARRAY[1,2]) | 1 2 (2 rows) |
行转列
用postgresql的crosstab交叉函数
-- 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（产生一组正态分布的随机值） |
| 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个值列的“数据透视表”，其中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个值列的“数据透视表”。交叉表2、交叉表3和交叉表4都是预定义的，但是您可以创建额外的跨表n函数，如下面所述） |
| crosstab(text source_sql, text category_sql) | setof record | Produces a “pivot table” with the value columns specified by a second query（生成具有由第二个查询指定的值列的“数据透视表”） |
| 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（过时版本的交叉表（文本）。参数N现在被忽略，因为值列的数量总是由调用查询决定） |
| 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（生成层次树结构的表示） |
总结
到此这篇关于postgresql行转列与列转行的文章就介绍到这了,更多相关postgresql行转列 列转行内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！
