简单实现PostgreSQL统计数据行转列-[PostgreSQL行转列]

注:PostgreSQL 9.3版本以上才支持join lateral

• 语法

WITH T_DATA AS(
 <查询统计数据的SQL>
)
select t.* from T_DATA 
cross join lateral
(values
	('KEY列的键名称',KEY列的键对应的值),
) as t(KEY,VAL)

• 示例:
  • 统计SQL:

   SELECT
    20 SUCCESS_RATE,
    100 TOTAL_NUMBER,
    30 INIT_NUM,
    20 FAIL_NUM,
    20 SUCCESS_NUM,
    30 EXPIRED_NUM
  

  • 统计SQL查询的结果:
    
  • 统计数据行转列SQL:

 WITH T_DATA AS(
 SELECT
	20 SUCCESS_RATE,
	100 TOTAL_NUMBER,
	30 INIT_NUM,
	20 FAIL_NUM,
	20 SUCCESS_NUM,
	30 EXPIRED_NUM
)
select t.* from T_DATA 
cross join lateral
(values
	('成功率',SUCCESS_RATE),
	('总笔数',TOTAL_NUMBER),
	('初始状态数',INIT_NUM),
	( '失败数',FAIL_NUM),
	('成功数',SUCCESS_NUM),
	('过期数',EXPIRED_NUM)
) as t(KEY,VAL)

结果 :