本文详细介绍了在Oracle数据库中如何进行行转列和列转行的操作。通过实例展示了如何使用DECODE或CASE...WHEN结合GROUP BY实现行转列,以及如何利用UNION和INSERT ALL INTO SELECT进行列转行。此外,还提到了Oracle 11g引入的PIVOT和UNPIVOT函数,以更简洁的方式完成转换。
摘要由CSDN通过智能技术生成
行转列
table
DROP TABLE "DEMO_USER"."BR_CLAIM_PROPORTION_BUDGET";
CREATE TABLE "DEMO_USER"."BR_CLAIM_PROPORTION_BUDGET" (
"ID" VARCHAR2(64 BYTE) NOT NULL ,
"DATE_TIME" DATE NULL ,
"COMPANY_ID" VARCHAR2(20 BYTE) NULL ,
"CREATE_TIME" DATE NULL ,
"UPDATE_TIME" DATE NULL ,
"DEL_FLAG" VARCHAR2(1 BYTE) DEFAULT '0' NULL ,
"LINK" NUMBER NULL ,
"PROPORTION" NUMBER(10,4) NULL
);
COMMENT ON TABLE "DEMO_USER"."BR_CLAIM_PROPORTION_BUDGET" IS '各环节责任承担比例预算';
COMMENT ON COLUMN "DEMO_USER"."BR_CLAIM_PROPORTION_BUDGET"."DATE_TIME" IS '预算时间';
COMMENT ON COLUMN "DEMO_USER"."BR_CLAIM_PROPORTION_BUDGET"."COMPANY_ID" IS '责任单位id(省份或集团),对应b_owner的owner_id';
COMMENT ON COLUMN "DEMO_USER"."BR_CLAIM_PROPORTION_BUDGET"."CREATE_TIME" IS '创建时间';
COMMENT ON COLUMN "DEMO_USER"."BR_CLAIM_PROPORTION_BUDGET"."UPDATE_TIME" IS '修改时间';
COMMENT ON COLUMN "DEMO_USER"."BR_CLAIM_PROPORTION_BUDGET"."DEL_FLAG" IS '逻辑删除,0正常,1删除';
COMMENT ON COLUMN "DEMO_USER"."BR_CLAIM_PROPORTION_BUDGET"."LINK" IS '责任环节(0公司、1平台、2直营、3特许、4干线)';
COMMENT ON COLUMN "DEMO_USER"."BR_CLAIM_PROPORTION_BUDGET"."PROPORTION" IS '所占比例';
-- ----------------------------
-- Records of BR_CLAIM_PROPORTION_BUDGET
-- ----------------------------
INSERT INTO "DEMO_USER"."BR_CLAIM_PROPORTION_BUDGET" VALUES ('4', TO_DATE('2020-03-01 16:37:49', 'YYYY-MM-DD HH24:MI:SS'), '1551001', null, TO_DATE('2020-03-11 18:54:36', 'YYYY-MM-DD HH24:MI:SS'), '0', '1', '0.20');
INSERT INTO "DEMO_USER"."BR_CLAIM_PROPORTION_BUDGET" VALUES ('6', TO_DATE('2020-03-01 16:37:49', 'YYYY-MM-DD HH24:MI:SS'), '1551001', null, TO_DATE('2020-03-11 18:54:36', 'YYYY-MM-DD HH24:MI:SS'), '0', '4', '0.20');
INSERT INTO "DEMO_USER"."BR_CLAIM_PROPORTION_BUDGET" VALUES ('5', TO_DATE('2020-03-01 16:37:49', 'YYYY-MM-DD HH24:MI:SS'), '1551001', null, TO_DATE('2020-03-11 18:54:36', 'YYYY-MM-DD HH24:MI:SS'), '0', '2', '0.30