PostgreSQL 多列转多行，列转行，使用pg自带string_to_array方式实现

PostgreSQL 多列转多行，列转行，使用pg自带string_to_array方式实现

在一些场景下，需要用到把多列转换为多行进行展示

SELECT
recorid,
objectid,
month1,
month2,
month3,
month4,
month5,
month6,
month7,
month8,
month9,
month10,
month11,
month12,
year
from monthtarget

转换前

转换sql

SELECT 
objectid,
year,
unnest(string_to_array((
COALESCE(month1,'0')||','||
COALESCE(month2,'0')||','||
COALESCE(month3,'0')||','||
COALESCE(month4,'0')||','||
COALESCE(month5,'0')||','||
COALESCE(month6,'0')||','||
COALESCE(month7,'0')||','||
COALESCE(month8,'0')||','||
COALESCE(month9,'0')||','||
COALESCE(month10,'0')||','||
COALESCE(month11,'0')||','||
COALESCE(month12,'0')) ,',')) target,
year||'-'||unnest(string_to_array('01,02,03,04,05,06,07,08,09,10,11,12',',')) yearmonth
FROM monthtarget

转换后