纵表和横表的相互转换

竖表结构

CREATE TABLE "public"."material_info_1678183618765" (
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "mat_id" varchar(255) COLLATE "pg_catalog"."default",
  "material_type" varchar(255) COLLATE "pg_catalog"."default",
  "data_id" varchar(255) COLLATE "pg_catalog"."default",
  "values" varchar(255) COLLATE "pg_catalog"."default",
  "remark" varchar(255) COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "create_by" varchar(64) COLLATE "pg_catalog"."default",
  "update_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "update_by" varchar(64) COLLATE "pg_catalog"."default",
  "del_flag" char(1) COLLATE "pg_catalog"."default",
  "tenant_id" int8
)
;

ALTER TABLE "public"."material_info_1678183618765" 
  OWNER TO "postgres";

COMMENT ON COLUMN "public"."material_info_1678183618765"."id" IS '主键';

COMMENT ON COLUMN "public"."material_info_1678183618765"."mat_id" IS '物资表主键';

COMMENT ON COLUMN "public"."material_info_1678183618765"."material_type" IS '物资类型';

COMMENT ON COLUMN "public"."material_info_1678183618765"."data_id" IS '物资属性id';

COMMENT ON COLUMN "public"."material_info_1678183618765"."values" IS '物资属性值';

COMMENT ON COLUMN "public"."material_info_1678183618765"."remark" IS '备注';

COMMENT ON COLUMN "public"."material_info_1678183618765"."create_time" IS '创建时间';

COMMENT ON COLUMN "public"."material_info_1678183618765"."create_by" IS '创建人';

COMMENT ON COLUMN "public"."material_info_1678183618765"."update_time" IS '修改时间';

COMMENT ON COLUMN "public"."material_info_1678183618765"."update_by" IS '修改人';

COMMENT ON COLUMN "public"."material_info_1678183618765"."del_flag" IS '是否删除（0-否；1-是）';

COMMENT ON COLUMN "public"."material_info_1678183618765"."tenant_id" IS '租户ID';

数据字典表结构

CREATE TABLE "public"."material_dict_data" (
  "material_code" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "material_sort" int4,
  "material_label" varchar(100) COLLATE "pg_catalog"."default",
  "material_value" varchar(100) COLLATE "pg_catalog"."default",
  "material_type" varchar(100) COLLATE "pg_catalog"."default",
  "is_import" char(1) COLLATE "pg_catalog"."default",
  "status" char(1) COLLATE "pg_catalog"."default",
  "create_by" varchar(64) COLLATE "pg_catalog"."default",
  "create_time" timestamp(6),
  "update_by" varchar(64) COLLATE "pg_catalog"."default",
  "update_time" timestamp(6),
  "remark" varchar(500) COLLATE "pg_catalog"."default",
  "del_flag" char(1) COLLATE "pg_catalog"."default",
  "is_public" char(1) COLLATE "pg_catalog"."default",
  "input_type" varchar(64) COLLATE "pg_catalog"."default",
  "is_validate" char(1) COLLATE "pg_catalog"."default",
  "tenant_id" int8,
  "dict_type" json
)
;

ALTER TABLE "public"."material_dict_data" 
  OWNER TO "postgres";

COMMENT ON COLUMN "public"."material_dict_data"."material_code" IS '主键';

COMMENT ON COLUMN "public"."material_dict_data"."material_sort" IS '排序值';

COMMENT ON COLUMN "public"."material_dict_data"."material_label" IS '物资标签';

COMMENT ON COLUMN "public"."material_dict_data"."material_value" IS '物资键值';

COMMENT ON COLUMN "public"."material_dict_data"."material_type" IS '物资类型';

COMMENT ON COLUMN "public"."material_dict_data"."is_import" IS '是否导入字段（0-否；1-是）';

COMMENT ON COLUMN "public"."material_dict_data"."status" IS '状态（0-启用；1-停用）';

COMMENT ON COLUMN "public"."material_dict_data"."create_by" IS '创建人';

COMMENT ON COLUMN "public"."material_dict_data"."create_time" IS '创建时间';

COMMENT ON COLUMN "public"."material_dict_data"."update_by" IS '修改人';

COMMENT ON COLUMN "public"."material_dict_data"."update_time" IS '修改时间';

COMMENT ON COLUMN "public"."material_dict_data"."remark" IS '备注';

COMMENT ON COLUMN "public"."material_dict_data"."del_flag" IS '是否删除（0-否；1-是）';

COMMENT ON COLUMN "public"."material_dict_data"."is_public" IS '是否公共配置（0-否；1-是）';

COMMENT ON COLUMN "public"."material_dict_data"."input_type" IS '输入类型';

COMMENT ON COLUMN "public"."material_dict_data"."is_validate" IS '是否验证（0-否；1-是）';

COMMENT ON COLUMN "public"."material_dict_data"."tenant_id" IS '租户ID';

COMMENT ON COLUMN "public"."material_dict_data"."dict_type" IS '下拉列表字典类型';

COMMENT ON TABLE "public"."material_dict_data" IS '物资参数表';

竖表转横表

SELECT
    i.mat_id,
    ( string_agg ( CASE d.material_value WHEN 'oldMatNo' THEN i.values ELSE '' END,'' ) )  AS oldMatNo,
    ( string_agg ( CASE d.material_value WHEN 'enterPort' THEN i.values ELSE '' END,'' ) )  AS enterPort,
    ( string_agg ( CASE d.material_value WHEN 'needPrint' THEN i.values ELSE '' END,'' ) )  AS needPrint,
    ( string_agg ( CASE d.material_value WHEN 'centraPurch' THEN i.values ELSE '' END,'' ) )  AS centraPurch,
    ( string_agg ( CASE d.material_value WHEN 'qualityIO' THEN i.values ELSE '' END,'' ) )  AS qualityIO,
    ( string_agg ( CASE d.material_value WHEN 'purTeam' THEN i.values ELSE '' END,'' ) )  AS purTeam,
    ( string_agg ( CASE d.material_value WHEN 'purCycle' THEN i.values ELSE '' END,'' ) )  AS purCycle,
    ( string_agg ( CASE d.material_value WHEN 'blueprint' THEN i.values ELSE '' END,'' ) )  AS blueprint
FROM
    material_info_1678183618765 i
LEFT JOIN material_dict_data d on d.material_code = i.data_id and d.material_type = i.material_type
GROUP BY 
    i.mat_id