PostgreSQL使用CROSSTAB行转列报无效源SQL语句错误如何解决

问题原因 #

你触发报错的核心原因是PostgreSQL的CROSSTAB函数对输入的源查询有严格格式要求：单参数版CROSSTAB要求源查询必须严格返回3列，顺序依次为行ID（用来区分不同行的唯一标识）、分类列（用来转成最终结果的列名）、值列（最终填充到单元格的内容）。你原来的源查询返回了GROUP_DEST、DEST、TIER、RATE4列，不符合要求所以触发报错。

解决方法 #

由于你需要用GROUP_DEST和DEST两个字段共同作为行的唯一标识，可以先把两个字段拼接为单一的行ID字段，满足CROSSTAB的入参要求，查询结果返回后再拆分即可得到分开的两个字段。推荐使用双参数版CROSSTAB，显式指定分类列的取值顺序，避免数据缺失时出现列错位的问题，修改后的语句如下：

SELECT 
  SPLIT_PART(row_id, '||', 1) AS GROUP_DEST,
  SPLIT_PART(row_id, '||', 2) AS DEST,
  TIER_1,
  TIER_2
FROM CROSSTAB(
  -- 第一参数：返回行ID、分类、值三列的源查询
  'SELECT CONCAT(GROUP_DEST, ''||'', DEST) AS row_id, TIER::TEXT, RATE::TEXT 
   FROM VBB_TIER 
   ORDER BY 1',
  -- 第二参数：显式指定分类列的取值顺序，避免列错位
  'SELECT DISTINCT TIER FROM VBB_TIER ORDER BY TIER'
) AS CT(
  row_id TEXT,
  TIER_1 TEXT, 
  TIER_2 TEXT 
);

如果你不需要拆分两个字段，也可以直接用拼接后的行ID作为第一列：

SELECT *
FROM CROSSTAB(
  'SELECT CONCAT(GROUP_DEST, ''||'', DEST) AS row_id, TIER::TEXT, RATE::TEXT 
   FROM VBB_TIER 
   ORDER BY 1, 2'
) AS CT(
  row_id TEXT,
  TIER_1 TEXT, 
  TIER_2 TEXT 
);

内容的提问来源于stack exchange，提问作者Deepak Patil