PostgreSql数据库-行转列函数crosstab

PG数据库行转列函数crosstab操作

原数据：

 

第一步:

对表数据空值进行处理(我这里是对空值填写字符串)

第二步:

执行CREATE EXTENSION tablefunc，如果不执行这个sql，会报错crosstab(unknown, unknown) does not exist，作用如下图：

第三步：

 

 

代码:

select * from 
crosstab('select id::varchar,json_key::varchar,json_values::varchar from pssf.key_values ORDER BY 1',
     'select  json_key::varchar from pssf.key_values ORDER BY 1')
as ct(
id varchar(50000),longitude varchar(50000),
MONITORTYPEMANY varchar(50000),
REVIEWUSER varchar(50000),
PRODUCTRISK varchar(50000),
ISLITTLE varchar(50000),
NEEDINSURENUMBER varchar(50000),
OTHER varchar(50000),
STATEMESSAGE varchar(50000),
DELETETIME varchar(50000),
wgs84_x varchar(50000),
RECORDOPINION varchar(50000),
ISARCHIVES varchar(50000),
SUPERVISEDEPTID varchar(50000),
STATEREASON varchar(50000),
MONITORTYPEDETAIL varchar(50000),
DELETEREASON varchar(50000),
RATEPAYERSTATE varchar(50000),
ACREAGE varchar(50000),
REGISTERMONTY varchar(50000),
SPECITYENTP varchar(50000),
ENTPKIND varchar(50000),
GRADETIMEBEGIN varchar(50000),
RECORDDATE varchar(50000),
INSURENUMBER varchar(50000),
UNITTEL varchar(50000),
PUREWORK varchar(50000),
MAINCRAFT varchar(50000),
GRADECHECKDATE varchar(50000),
MANAGERUNIT varchar(50000),
LINKTEL varchar(50000),
POLICYNAME varchar(50000),
RECORDRESULT varchar(50000),
UPLOADTIME varchar(50000),
GRIDID varchar(50000),
get_info_msg varchar(50000),
MAPX varchar(50000),
DELETETYPE varchar(50000),
latitude varchar(50000),
HPHAZARDPOINT_STATUS varchar(50000),
FAXTELEPHONE varchar(50000),
ISSIZE varchar(50000),
ENTPNAME varchar(50000),
TRADECODEDETAIL varchar(50000),
is_space varchar(50000),
ISRATE varchar(50000),
REVIEWDATE varchar(50000),
DELETEDEPT varchar(50000),
MODELENTPFLAG varchar(50000),
SCALEKIND varchar(50000),
LINKNAME varchar(50000),
POLICYTEL varchar(50000),
AREACODE_JURISDICTION varchar(50000),
EXAMDATE varchar(50000),
FIXEDASSETS varchar(50000),
GRID varchar(50000),
POSTCODE varchar(50000),
SUPERVISEDEPTNAME varchar(50000),
FILLDATE varchar(50000),
EXAMSTATE varchar(50000),
SIMPLENAME varchar(50000),
ISSAFEINSU varchar(50000),
ISGRADE varchar(50000),
ISSUBLETENTP varchar(50000),
LASTYEARINCOME varchar(50000),
FILLMAN varchar(50000),
RELATIONS varchar(50000),
INNERID varchar(50000),
GRADE varchar(50000),
ENTPCODE varchar(50000),
INDUSTRIALPARK varchar(50000),
SCJYDWLX varchar(50000),
LEVELSTATE varchar(50000),
REVIEWDEPTNAME varchar(50000),
TEMPNUM varchar(50000),
UPDATEBY varchar(50000),
MANAGEHIGHTNUMBER varchar(50000),
FILLTEL varchar(50000),
PY varchar(50000),
ECOTYPE varchar(50000),
LESSORNAME varchar(50000),
LABEL varchar(50000),
HAVECREDENTIAL varchar(50000),
error_msg varchar(50000),
LITTLETYPE varchar(50000),
DUSTMAN varchar(50000),
MANAGEZONE varchar(50000),
HAZARDPROPERTY varchar(50000),
PUCENTPSCORE varchar(50000),
REGISTERDEPT varchar(50000),
INDUSTRYTYPE varchar(50000),
RECORDID varchar(50000),
MONITORTYPE varchar(50000),
BUILDINGCODE varchar(50000),
GRADECHECKMAN varchar(50000),
DUSTDATE varchar(50000),
GRADETIMEEND varchar(50000),
SPECIALJOBBOOKNUM varchar(50000),
get_info_count varchar(50000),
PROJECTNAME varchar(50000),
WEBSITE varchar(50000),
ISSAFEMONITORENTP varchar(50000),
PRODUCTIONRIGHT varchar(50000),
ISHUNDRED varchar(50000),
LESSORTEL varchar(50000),
REGISTERSAFEENGINEER varchar(50000),
EXAMOPINION varchar(50000),
REGISTERADDR varchar(50000),
WJ_ varchar(50000),
LITTLEINDUSTRYTYPE varchar(50000),
PINNERID varchar(50000),
MANAGERDPT varchar(50000),
VALIDDATE_START varchar(50000),
F_KEY varchar(50000),
X5_TEST2 varchar(50000),
REVIEWMAN varchar(50000),
REGISTERUSER varchar(50000),
UPDATETIME varchar(50000),
X5_TEST1 varchar(50000),
ROOMCODE varchar(50000),
MININGWAY varchar(50000),
DELETEBY varchar(50000),
AREACODE varchar(50000),
MAPY varchar(50000),
RISKLEVEL varchar(50000),
EXAMDEPT varchar(50000),
REGISTERID varchar(50000),
TOTALEARNING varchar(50000),
ISFIREKEY varchar(50000),
EMPQTY varchar(50000),
TRADECON varchar(50000),
RECORDDEPT varchar(50000),
MAINPRODUCT varchar(50000),
DELETEFLAG varchar(50000),
REGISTERDATE varchar(50000),
wgs84_y varchar(50000),
STAFFNUM varchar(50000),
ADDRESS varchar(50000),
PENTPNAME varchar(50000),
EMAIL varchar(50000),
CREATEBY varchar(50000),
BUILDINGCODENAME varchar(50000),
SAFEEDUQTY varchar(50000),
MAINMATERIAL varchar(50000),
ENTPSIZE varchar(50000),
INNERIDTEMP varchar(50000),
CREATETIME varchar(50000),
RATE varchar(50000),
ADDRESSCODE varchar(50000),
PRODUCTLINENUM varchar(50000),
EMPLOYEENUM varchar(50000),
SPECIALJOBNUM varchar(50000),
ISKEY varchar(50000),
LEV varchar(50000),
VALIDDATE_END varchar(50000),
DUSTDEPT varchar(50000),
ISSYNO varchar(50000),
DUSTUSER varchar(50000),
PUCENTPGRADE varchar(50000),
STATE varchar(50000),
XH varchar(50000),
ENTERPRISESTATE varchar(50000),
MAINPRODUCTION varchar(50000),
STATEDATE varchar(50000),
FILLENTPNAME varchar(50000))

官方文档：PostgreSQL: Documentation: 11: F.38. tablefunc