SQL 行转列 和 列转行 整理

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.厂库名称