PostgresSQL将多行转成一行多列

-- 创建 adi 状态、类型表
DROP TABLE IF EXISTS adi;
DROP SEQUENCE IF EXISTS adi;
CREATE SEQUENCE s_adi;
CREATE TABLE "public"."adi" (
  "id" int4 NOT NULL DEFAULT nextval('ac_device_info'),
  "device_sys_code" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
  "device_name" varchar(128) COLLATE "pg_catalog"."default",
  "device_type" int4 DEFAULT 0,
  "device_status" int4 DEFAULT 0,
  CONSTRAINT "adi_pkey" PRIMARY KEY ("id")
);
ALTER TABLE "public"."adi" OWNER TO "test";
COMMENT ON COLUMN "public"."adi"."id" IS '主键id';
COMMENT ON COLUMN "public"."adi"."device_sys_code" IS '编码';
COMMENT ON COLUMN "public"."adi"."device_name" IS '名称';
COMMENT ON COLUMN "public"."adi"."device_type" IS '类型：0 普通，1 特殊';
COMMENT ON COLUMN "public"."adi"."device_status" IS '状态：0 离线，1 在线';
COMMENT ON TABLE "public"."adi" IS '状态类、型表';

--创建视图 v_adis
DROP VIEW IF EXISTS v_adis CASCADE;
CREATE OR REPLACE VIEW "public"."v_adis" AS
SELECT
    split_part(string_agg(concat(countInfo.device_count, ''), ',') , ',', 1) AS common_offline_count,-- 00数量
    split_part(string_agg(concat(countInfo.device_count, ''), ',') , ',', 2) AS common_online_count, -- 01数量
    split_part(string_agg(concat(countInfo.device_count, ''), ',') , ',', 3) AS face_offline_count,  -- 10数量
    split_part(string_agg(concat(countInfo.device_count, ''), ',') , ',', 4) AS face_online_count    -- 11数量
FROM (
    SELECT 
        concat(adi.device_type, adi.device_status) AS device_tt,
        COUNT( * ) AS device_count
    FROM
        adi
    GROUP BY
        adi.device_type,
        adi.device_status   
        ORDER BY device_tt
) countInfo;
ALTER TABLE "public"."v_adis" OWNER TO "test";
grant select on v_adis TO third_user;