SQL 列转行 和行转列
列转多行场景:
表中的某个字段是由多个字符串拼接而成,需要按照每个拼接的字符串转换成多行,比如 :【手机品牌】字段里的内容是 小米,华为,苹果;按照这个几个类别进行分组;
原始数据:
ID | 厂库名称 | 厂库地址 | 手机品牌类型 |
---|---|---|---|
1 | 手机厂库 | xxx路xxx街道 | 小米,华为,苹果 |
目标数据:
ID | 厂库名称 | 厂库地址 | 手机品牌类型 |
---|---|---|---|
1 | 手机厂库 | xxx路xxx街道 | 小米 |
1 | 手机厂库 | xxx路xxx街道 | 华为 |
1 | 手机厂库 | xxx路xxx街道 | 苹果 |
ORACLE
select trim(regexp_substr(T.手机品牌, '[^,]+', 1, levels.column_value)
from 表名 T,table(cast(multiset(select level from dual connect by level <= length(regexp_replace(t.ERROR_RULE, '[^,]+')) + 1) as sys.OdciNumberList)) levels
SPARK
select explode(split(T.手机品牌,",") from 表名
MYSQL
select substring_index(substring_index(T.手机品牌,',', h.id + 1 ),',',-1)
from 表名 T
JOIN TABLE_HELP h ON h.id < (length(T.手机品牌) - length(REPLACE(T.手机品牌,',','')) + 1);
/*==============================================================*/
/* Table: TABLE_HELP */
/*==============================================================*/
CREATE TABLE TABLE_HELP (
ID int(4) NOT NULL comment '个数',
primary key (ID)
);
comment on table TABLE_HELP is
'列转行协助表';
-- ----------------------------
-- Records of TABLE_HELP
-- ----------------------------
INSERT INTO TABLE_HELP VALUES (0);
INSERT INTO TABLE_HELP VALUES (1);
INSERT INTO TABLE_HELP VALUES (2);
INSERT INTO TABLE_HELP VALUES (3);
INSERT INTO TABLE_HELP VALUES (4);
INSERT INTO TABLE_HELP VALUES (5);
INSERT INTO TABLE_HELP VALUES (6);
INSERT INTO TABLE_HELP VALUES (7);
INSERT INTO TABLE_HELP VALUES (8);
.....
多行转列场景:
其实 就是跟上面的场景 反过来:
原始数据:
ID | 厂库名称 | 厂库地址 | 手机品牌类型 |
---|---|---|---|
1 | 手机厂库 | xxx路xxx街道 | 小米 |
1 | 手机厂库 | xxx路xxx街道 | 华为 |
1 | 手机厂库 | xxx路xxx街道 | 苹果 |
目标数据:
ID | 厂库名称 | 厂库地址 | 手机品牌类型 |
---|---|---|---|
1 | 手机厂库 | xxx路xxx街道 | 小米,华为,苹果 |
ORACLE
select WM_CONCAT(T.手机品牌类型) from 表名 T group by T.厂库名称
SPARK
select concat_ws(',',collect_list(T.手机品牌类型)) from 表名 T group by T.厂库名称
MYSQL
select GROUP_CONCAT(T.手机品牌类型,',') from 表名 T group by T.厂库名称