oracle行转列、oracle列转行 以及 mysql列转行,mysql行转列
前言
一篇文章总结举例介绍oracle和mysql的行转列、列转行操作
一、oracle:逗号分隔的一列转行
建表语句:
CREATE TABLE "GZY"."T_CLOUD_INQUIRY_CLASSIFY" (
"ID" VARCHAR2(100 BYTE) NOT NULL,
"NAME" VARCHAR2(100 BYTE),
"INQUIRY" VARCHAR2(800 BYTE),
"JGBM" VARCHAR2(100 BYTE),
"CZJG" VARCHAR2(100 BYTE),
"CZR" VARCHAR2(100 BYTE),
"CZSJ" VARCHAR2(100 BYTE),
"CZZT" VARCHAR2(100 BYTE),
"OPENFLAG" VARCHAR2(100 BYTE)
)
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
INITIAL 196608
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
数据库insert语句:
INSERT INTO "" ("INQUIRY", "ID", "NAME", "INQUIRY", "JGBM", "CZJG", "CZR", "CZSJ", "CZZT", "OPENFLAG") VALUES ('77792054EB504A3089C1F95C2252DD02,D43EB32DF99141D19644E6F33780F6D7,41F4ACA5AD87467D9ADCF128DBD74C2D,F7566A51790443DF94912A4CBBC4DC0E', '70EC1DA5186D4E4583EB6C98815AFAB7', '问题分类2', '77792054EB504A3089C1F95C2252DD02,D43EB32DF99141D19644E6F33780F6D7,41F4ACA5AD87467D9ADCF128DBD74C2D,F7566A51790443DF94912A4CBBC4DC0E', '110100990302010001', '610100030101010001', '100000', '2022-05-19 14:34:35', '1', '1');
建好后的数据库表如下(示例):
逗号分隔的一列转多行语句:
SELECT
REGEXP_SUBSTR( INQUIRY, '[^,]+', 1, L ) INQUIRY,
id,
jgbm
FROM
T_CLOUD_INQUIRY_CLASSIFY t,
( SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100 )
WHERE
L ( + ) <= LENGTH( INQUIRY ) - LENGTH( REPLACE ( INQUIRY, ',' ) ) + 1
AND id = '70EC1DA5186D4E4583EB6C98815AFAB7'
查询后的数据根据字段的逗号分隔变成多行:
二、oracle:多列转行 unpivot 函数
原表
列转行后-效果
unpivot(value FOR times IN(TIME_1A, TIME_2A, TIME_3A,
TIME_4P, TIME_5P, TIME_6P))
unpivot(列名(列的值) for 列名(列得名称) in (需转为行的列名));
三、oracle:行转列 pivot 函数
语法:pivot(任一聚合函数 for 需转列的值所在列名 in (需转为列名的值));
行转列后的效果
四、oracle:多行转一列 wm_concat 函数
原表
select wm_concat(to_char(name)) as value from SYS_RESOURCE;
select replace(wm_concat(diag_code),',',';') diag_code from Hzst_Diagnosis;
行转列后的效果