本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

《PostgreSQL技术问答00 - Why Postgres》

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文主要讨论的内容是在Postgres中，一个比较冷门的知识和内容：行转列。实际上，虽然看起来这个功能比较冷门，但如果能够理解和掌握，在某些特定的场景中，也是非常有效和方便的。比如本文能够成文，就是因为笔者在实际工作中有一个场景用到了这个功能，笔者才会有一定的研究和了解，才有了本文比较充实的内容。

什么是行转列

“行转列”，从字面上理解，就是将数据库中的数据，从以行常规的进行存储和展示的形式（就是标准的数据库记录集），转换成为以行和列，就是在工作中常见的表格的形式来展示。这里的行就变成了对象，而列是对象的属性，行和列对应的值，就是属性的值。

下面举个例子，让读者能够更加直观的理解。就拿我们熟悉的考试科目成绩表来说，在数据库中，通常的数据在数据库表中的存储结构如下：

学生科目成绩

刘备

语文

98

关羽

语文

92

张飞

语文

90

刘备

数学

63

张飞

数学

74

曹操

数学

85

刘备

英语

76

关羽

英语

88

曹操

英语

90

但在实际业务中，我们可能更习惯这样的展现方式：

学生\科目语文数学英语

刘备

98

63

76

关羽

92

--

88

张飞

90

74

--

曹操

--

85

90

从逻辑角度和信息承载的实质而言，其实两者没有本质区别，只是在业务上更加清晰明了，用户体验更好。从这个角度而言，就是将以行为主要形式的数据库方式，转换成为Excel表格的形式，每列是一个对象属性。也是将一维的数据结构，转换成为更容易观察和理解的二维数据结构。我们将这个呈现形式的转换，可以简称为“行转列”。

那么问题在于，为什么不一开始，就将数据表设计成为属性列的方式呢？笔者理解，通常这样处理，可能是考虑以下的因素：

在数据设计的时候，不需要预先设计好具体的结构，只需要设计好"对象-属性-值"这样数据结构框架，就可以保持一种抽象和灵活性。

抽象的目的是可以进行方便的扩展。比如如果要增加一个科目，不需要改变数据库表结构，直接增加对应属性值和数据就可以了。

关系型数据库使用记录行方式进行数据管理的一大特点，是可以方便的在纵向维度上，结合数据特性，进行快速的分离和汇总计算，从而满足各种各样的业务需求。

如何操作和实现

在初步的理解了“行转列”的概念之后，我们随即结合前面实际数据和案例，来进行实际的操作和分析。相关的操作代码如下：

-- 创建扩展 
CREATE EXTENSION IF NOT EXISTS tablefunc;
-- 行转列查询
select * from crosstab(
'select student, course, score from ctest order by 1, 2',
'select distinct course from ctest order by 1'

) as ct( student varchar, sx varchar, yw varchar,  yy varchar  );
-- 查询结果
student   sx      yw      yy
关羽		92	88
刘备	63	98	76
张飞	74	90	
曹操	85		90

在开始分析之前，我们可以先来从逻辑上构想一下，这个行转列将如何操作，这有助于我们理解实际代码的编写和实现。

首先，我们可以想象，我们想要的一个结果，就是一个表，表的内容来自原始记录集，但将其转换成为了一个二维结构。表的第一个字段(第一列)，就是学生的列表；而表的第一行就是表头，应该就是所有的科目；而表的单元格的内容值，就是以学生和科目为依据，查询的成绩数值。所以，无论实际的具体实现的形式如何，都应该包括上面分析的要素。

然后我们就可以深入分析和理解crosstab的实现方式了。

这是一个前提设定。在Postgres中，“行专列”的功能，是通过Crosstab(交叉表)相关的功能实现的。而crosstab，并不是PG内置的功能特性，而是它的一个扩展，所以需要先在数据库中加载，才能够来使用。有趣的是，这个扩展的名字，并不是"crosstab"，而是叫"tablefunc"。 crosstab其实是在这个扩展加载后，提供的一个可以用于进行行转列操作的函数名字。

示例中是一个简单但是完整的crosstab的使用方式。其实这个结构包括三个部分。

crosstab函数是行专列操作的核心，它会返回一个记录集，就是转换后的结果表。我们随后深入分析。

我们需要通过select * 来读取表记录集中的内容。

这个语句，其实是在定义字段列表，就是表头了。

显然，这个结构的核心，是crosstab函数。这个函数其实包括两个参数，形式都是字符串也就是查询语句。

这个语句，其实是用于提取转置后的单元格值，它有一定的语法规则。其实是严格的三个字段，第一个字段是表的第一个列的值；第二个字段是表头(行)的值；第三个内容，就是列和行对应的单元格中的值。理解了这个，我们就可以方便的根据业务需求，编写合适的查询语句了。

此数使用的值查询语句就是：

'select student, course, score from ctest order by 1, 2'

这里也通过一个sql语句，从原始表中，提取出用于作为表头的字段名称和列表。

而此处使用的表头列表查询语句就是：

select distinct course from ctest order by 1

需要注意的问题

虽然已经比较清晰的理解了crosstab的原理和逻辑，但笔者实际使用的过程中，还是觉得有很多问题需要注意。

这个比较简单，如果列和行组合在原始记录里面没有，则当前的单元格的值就为空。

比如，如果在原始记录中，增加一门课程。而不改变字段的定义内容，系统将如何处理呢？ 和想象中，查询会忽略这个值不同，系统会抛出错误：

Detail: Query-specified return tuple has 4 columns but crosstab returns 5.

大体的意思是在语句中只定义了四个列，但实际查询产生了五个列，它认为是一种错误。避免出现这个错误呢？ 笔者觉得可以在原始查询中，限制查询范围，就是先过滤一下，保证不会出现多余的列。

就笔者的使用经验而言。在定义所选择的数据和字段的时候，需要进行严格的匹配。比如例子中查询学生的成绩信息，需要先对学生进行排序，然后对课程进行排序；在定义转列的字段，也需要对课程进行排序，两者必须匹配。如果没有这个排序，可能就不能得到想要的结果，比如下面的情况：

- 非排序查询
select * from crosstab(
'select student, course, score from ctest ',
'select distinct course from ctest order by 1'
) as ct( student varchar, sx varchar , yw varchar,  yy varchar  );
- 转置结果
刘备		98	
关羽		92	
张飞		90	
刘备	63		
张飞	74		
曹操	85		
刘备			76
关羽			88
曹操			90

笔者认为，这样的设计，是为了更好的处理数据，来提高转置操作的效率。crosstab函数，会简单的从前到后遍历查询结果集来构造结果数据，而不用考虑后续的处理，和可能的再次数据遍历。

如果需要预先使用和限定字段，显然示例中select distinct 方式，会导致不可预料的结果，这时可以考虑使用预定义数组，需要注意这里需要转换为记录集。相关的示例代码如下：

select * from crosstab(
$$ select student, course, score from ctest 
where course in ('yx','sx','yy') 
order by 1, 2 $$,
$$ select unnest(array['yw','sx','yy']) $$
) as ct( student varchar, yw varchar , sx varchar,  yy varchar  );

如果要在crosstab查询中使用文本类型的数据，一般需要使用单引号来表示，显然，这个和crosstab函数需要使用字符串sql作为参数就产生了冲突。这时我们可以使用postgres的 $$ 语句声明的方式来解决这个问题，就像上面的示例中展示的那样。

所以，要编写完整而且正确的转置操作，要特别注意这些细节和问题，才可能得到预期的结果。

小结

这一段参考了掘金提供的AI自动总结的内容。

本文主要讨论的内容是在Postgre中进行数据查询的行转列操作。包括了行转列的基本概念和理解，然后结合实际案例和代码展开讨论了其具体的操作和实现，包括创建扩展、相关代码及核心函数 crosstab 的使用和操作。还提到实际使用中需注意少值、多值、排序、限定类型和字段、单引号处理等细节方面的问题。