PostgreSQL实现将多行合并转为列

PostgreSQL实现将多⾏合并转为列
需求将下列表格相同id的name拼接起来输出成⼀列
id Name
1peter
1lily
2john
转化后效果：
id Name
1peter；lily
2john；
实现⽅式使⽤ 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
我们想要得到像这样的⼀张表：
姓名数学物理语⽂
李四849474
张三839380
当数据量⽐较少时，我们可以在Excel中使⽤数据透视表pivot table的功能实现这个需求，但当数据量较⼤，或者我们还需要在数据库中进⾏后续的数据处理时，使⽤数据透视表就显得不那么⾼效。

下⾯，介绍如何在Postgresql中实现数据的⾏转列。

静态写法
当我们要转换的值字段是数值型时，我们可以⽤SUM()函数：
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_score
GROUP BY 1
当我们要转换的值字段是字符型时，⽐如我们的表是这样的：
"Student_grade"表：
姓名数学物理语⽂
张三优良及格
李四良优及格
我们可以⽤string_agg()函数：
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 姓名
,string_agg((case 课程 when '数学' then 等级 end),'') as 数学
,string_agg((case 课程 when '物理' then 等级 end),'') as 物理
,string_agg((case 课程 when '语⽂' then 等级 end),'') as 语⽂
from Student_grade
GROUP BY 1
内置函数（半动态）
Postgresql内置了tablefunc可实现pivot table的功能。

语法：
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, ...);
例如：
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)，转成列
实现代码⽰例：
CREATE or REPLACE FUNCTION
long_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
以上为个⼈经验，希望能给⼤家⼀个参考，也希望⼤家多多⽀持。

如有错误或未考虑完全的地⽅，望不吝赐教。