SQL 将列标题转置为行的方法（PostgreSQL）

在本文中，我们将介绍如何使用SQL将表格中的列标题转置为行，在PostgreSQL数据库中实现这一功能。

阅读更多：SQL 教程

背景信息

在数据库中，通常使用表格来组织和存储数据。表格由行和列组成，每一列代表一种特定的数据类型或属性，而每一行则代表该属性对应的具体数值。在某些情况下，我们可能需要将表格中的列标题（也称为列名）转置为行，以满足特定的需求或数据分析要求。

方法一：使用UNION ALL联接多个SELECT语句

首先，我们可以使用UNION ALL操作符联接多个SELECT语句，每个SELECT语句查询一个特定列，并将列名作为一个新的行返回。下面是一个例子：

SELECT 'Column1' AS ColumnName, Column1 AS ColumnValue FROM YourTable
UNION ALL
SELECT 'Column2' AS ColumnName, Column2 AS ColumnValue FROM YourTable
UNION ALL
SELECT 'Column3' AS ColumnName, Column3 AS ColumnValue FROM YourTable
-- 连接更多的SELECT语句

请将上述例子中的YourTable替换为实际的表格名称，并将Column1、Column2、Column3等替换为实际的列名。通过联接多个SELECT语句，我们可以将每个列名作为一行返回，得到包含列名和对应列值的结果集。

需要注意的是，使用UNION ALL操作符时，每个SELECT语句的列数和数据类型必须匹配。

方法二：使用CROSSTAB扩展

在PostgreSQL中，我们可以使用CROSSTAB扩展来轻松地将列名转置为行。要使用CROSSTAB扩展，首先需要确保安装了tablefunc扩展。如果未安装，可以使用以下命令进行安装：

CREATE EXTENSION IF NOT EXISTS tablefunc;

有了tablefunc扩展后，我们可以使用CROSSTAB函数来实现列标题到行的转置。下面是一个例子：

SELECT *
FROM crosstab(
  'SELECT row_number() OVER () AS row_num, column_name, column_value
   FROM (
     SELECT ''Column1'' AS column_name, column1 AS column_value FROM YourTable
     UNION ALL
     SELECT ''Column2'' AS column_name, column2 AS column_value FROM YourTable
     UNION ALL
     SELECT ''Column3'' AS column_name, column3 AS column_value FROM YourTable
     -- 连接更多的SELECT语句
   ) subquery
   ORDER BY row_num, column_name'
) AS result(row_num integer, Column1 integer, Column2 integer, Column3 integer, ...);

同样，需要将上述例子中的YourTable替换为实际的表格名称，并将Column1、Column2、Column3等替换为实际的列名。通过将每个列名作为子查询的一部分，并将列值与之对应，我们可以使用CROSSTAB函数将列名转置为行。

需要注意的是，CROSSTAB函数的查询结果必须包含一个列用于标识每个行号。

方法三：使用动态SQL语句

另一种将列标题转置为行的方法是使用动态SQL语句。动态SQL允许我们在运行时构建SQL语句，并通过执行这些语句来实现转置。下面是一个使用动态SQL的例子：

-- 创建一个存储过程
CREATE OR REPLACE FUNCTION transpose_columns_to_rows(tablename text)
  RETURNS TABLE(column_name text, column_value text) AS DECLARE
  column_names text[];
  column_count int;
  i int;
BEGIN
  -- 获取表格中的所有列名
  SELECT array_agg(column_name), count(*) INTO column_names, column_count
  FROM information_schema.columns
  WHERE table_name = tablename;

  i := 1;
  -- 构建动态SQL语句，将每个列名转置为行
  WHILE i <= column_count LOOP
    RETURN QUERY EXECUTE format('SELECT ''%s'', %I FROM %I', column_names[i], column_names[i], tablename);
    i := i + 1;
  END LOOP;

  RETURN;
END; LANGUAGE plpgsql;

在上述例子中，我们创建了一个名为transpose_columns_to_rows的存储过程。该存储过程接受一个表格名称作为输入参数，并返回两列结果集，包含转置后的列名和对应的列值。

示例与注意事项

接下来我们通过示例来演示如何使用上述方法将列标题转置为行。

假设我们有一个名为employees的表格，包含以下列：employee_id, first_name, last_name, gender, hire_date。我们可以使用下面的SQL语句将列标题转置为行：

-- 方法一：使用UNION ALL
SELECT 'employee_id' AS ColumnName, employee_id AS ColumnValue FROM employees
UNION ALL
SELECT 'first_name' AS ColumnName, first_name AS ColumnValue FROM employees
UNION ALL
SELECT 'last_name' AS ColumnName, last_name AS ColumnValue FROM employees
UNION ALL
SELECT 'gender' AS ColumnName, gender AS ColumnValue FROM employees
UNION ALL
SELECT 'hire_date' AS ColumnName, hire_date AS ColumnValue FROM employees;

-- 方法二：使用CROSSTAB
SELECT *
FROM crosstab(
  'SELECT row_number() OVER () AS row_num, column_name, column_value
   FROM (
     SELECT ''employee_id'' AS column_name, employee_id AS column_value FROM employees
     UNION ALL
     SELECT ''first_name'' AS column_name, first_name AS column_value FROM employees
     UNION ALL
     SELECT ''last_name'' AS column_name, last_name AS column_value FROM employees
     UNION ALL
     SELECT ''gender'' AS column_name, gender AS column_value FROM employees
     UNION ALL
     SELECT ''hire_date'' AS column_name, hire_date AS column_value FROM employees
   ) subquery
   ORDER BY row_num, column_name'
) AS result(row_num integer, employee_id integer, first_name text, last_name text, gender text, hire_date date);

-- 方法三：通过调用存储过程
SELECT column_name, column_value
FROM transpose_columns_to_rows('employees');