PostgreSQL 实现将多行合并转为列

需求将下列表格相同id的name拼接起来输出成一列

转化后效果：

实现方式使用 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"表：

我们想要得到像这样的一张表：

当数据量比较少时，我们可以在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_scoreGROUP 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_gradeGROUP 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

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

• PostgreSQL 实现列转行问题
• PostgreSQL实现交叉表（行列转换）的5种方法示例
• postgresql高级应用之行转列& 汇总求和的实现思路

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！