需求将下列表格相同id的name拼接起来输出成一列
| id | Name |
| 1 | peter |
| 1 | lily |
| 2 | john |
转化后效果：
| id | Name |
| 1 | peter；lily |
| 2 | john； |
实现方式使用 array_to_string 和 array_agg 函数，具体语句如下：
string_agg(expression, delimiter) 把表达式变成一个数组
string_agg(expression, delimiter) 直接把一个表达式变成字符串
select id， array_to_string( array_agg(Name), ';' ) from table group by id
补充：Postgresql实现动态的行转列
问题
在数据处理中，常遇到行转列的问题，比如有如下的问题：
有这样的一张表
"Student_score"表：
| 姓名 | 课程 | 分数 |
|---|---|---|
| 张三 | 数学 | 83 |
| 张三 | 物理 | 93 |
| 张三 | 语文 | 80 |
| 李四 | 语文 | 74 |
| 李四 | 数学 | 84 |
| 李四 | 物理 | 94 |
我们想要得到像这样的一张表：
| 姓名 | 数学 | 物理 | 语文 |
|---|---|---|---|
| 李四 | 84 | 94 | 74 |
| 张三 | 83 | 93 | 80 |
当数据量比较少时，我们可以在Excel中使用数据透视表pivot table的功能实现这个需求，但当数据量较大，或者我们还需要在数据库中进行后续的数据处理时，使用数据透视表就显得不那么高效。
下面，介绍如何在Postgresql中实现数据的行转列。
静态写法
当我们要转换的值字段是数值型时，我们可以用SUM()函数：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
CREATE TABLE Student_score(姓名 varchar, 课程 varchar, 分数 int);INSERT INTO Student_score VALUES('张三','数学',83);INSERT INTO Student_score VALUES('张三','物理',93);INSERT INTO Student_score VALUES('张三','语文',80);INSERT INTO Student_score VALUES('李四','语文',74);INSERT INTO Student_score VALUES('李四','数学',84);INSERT INTO Student_score VALUES('李四','物理',94);select 姓名 ,sum(case 课程 when '数学' then 分数 end) as 数学 ,sum(case 课程 when '物理' then 分数 end) as 物理 ,sum(case 课程 when '语文' then 分数 end) as 语文from Student_scoreGROUP BY 1 |
当我们要转换的值字段是字符型时，比如我们的表是这样的：
"Student_grade"表：
| 姓名 | 数学 | 物理 | 语文 |
|---|---|---|---|
| 张三 | 优 | 良 | 及格 |
| 李四 | 良 | 优 | 及格 |
我们可以用string_agg()函数：
|
1
2
3
4
5
6
7
|
CREATE TABLE Student_grade(姓名 varchar, 课程 varchar, 等级 varchar);INSERT INTO Student_grade VALUES('张三','数学','优');INSERT INTO Student_grade VALUES('张三','物理','良');INSERT INTO Student_grade VALUES('张三','语文','及格');INSERT INTO Student_grade VALUES('李四','语文','及格');INSERT INTO Student_grade VALUES('李四','数学','良');INSERT INTO Student_grade VALUES('李四','物理','优'); |
select 姓名
|
1
2
3
4
5
|
,string_agg((case 课程 when '数学' then 等级 end),'') as 数学 ,string_agg((case 课程 when '物理' then 等级 end),'') as 物理 ,string_agg((case 课程 when '语文' then 等级 end),'') as 语文from Student_gradeGROUP BY 1 |
内置函数（半动态）
Postgresql内置了tablefunc可实现pivot table的功能。
语法：
|
1
2
3
4
5
6
|
SELECT *FROM crosstab( 'select row_name,cat,value from table order by 1,2')AS (row_name type, category_1 type, category_2 type, category_3 type, ...); |
例如：
|
1
2
3
4
5
6
|
SELECT *FROM crosstab( 'select 姓名,课程,分数 from Student_score order by 1,2')AS (姓名 varchar, 数学 int, 物理 int, 语文 int); |
需要注意的是crosstab( text sql) 中的sql语句必须按顺序返回row_name, category , value，并且必须声明输出的各列的列名和数据类型。当原表中的cat列有很多不同的值，那我们将会得到一个有很多列的表，并且我们需要手动声明每个列的列名及数据类型，显然这种体验非常不友好。那有没有更好的方式呢，我们可以通过手动建立存储过程（函数）实现。
自建函数（动态）
动态的行转列我们通过plpgsql实现，大致的思路如下：
判断value字段的数据类型，如果是数值型，则转入2.，否则转入3.
对cat列中的每个distinct值使用sum(case when)，转成列
对cat列中的每个distinct值使用string_agg(case when)，转成列
实现代码示例：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
CREATE or REPLACE FUNCTIONlong_to_wide(table_name VARCHAR,row_name VARCHAR,cat VARCHAR,value_field VARCHAR)returns void as$$/*table_name : 表名row_name : 行名字段cat : 转换为列名的字段value_field : 转换为值的字段*/DECLARE v_sql text;arow record;value_type VARCHAR;BEGIN v_sql=' drop table if exists temp_table; CREATE TABLE temp_table as SELECT distinct '||cat||' as col from '||table_name||' order by '||cat; execute v_sql; v_sql=' SELECT t.typname AS type FROM pg_class c ,pg_attribute a ,pg_type t WHERE c.relname = lower('''||table_name||''') and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid and a.attname='''||value_field||''' ORDER BY a.attnum '; execute v_sql into value_type;--获取值字段的数据类型 v_sql='select '||row_name; IF value_type in ('numeric','int8','int4','int')--判断值字段是否是数值型 THEN FOR arow in (SELECT col FROM temp_table) loop v_sql=v_sql||' ,sum(case '||cat||' when '''||arow.col||''' then '||value_field||' else 0 end) '||cat||'_'||arow.col; end loop; ELSE FOR arow in (SELECT col FROM temp_table) loop v_sql=v_sql||' ,string_agg((case '||cat||' when '''||arow.col||''' then '||value_field||' else '''' end),'''') '||cat||'_'||arow.col; end loop; END IF; v_sql=' drop table if exists '||table_name||'_wide; CREATE TABLE '||table_name||'_wide as '||v_sql||' from '||table_name||' group by '||row_name||'; drop table if exists temp_table '; execute v_sql;end;$$ LANGUAGE plpgsql; |
调用示例：
SELECT long_to_wide('Student_grade', '姓名','课程', '等级')
生成的表名为Student_grade_wide
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://www.cnblogs.com/karl-F/p/9182566.html
