hive，mysql，sqlserver 列转行函数，一行转成多行；行转列函数，多行合并成一行

一、列转行

1.hive 列转行实现方式

原始数据：
create table test(
name string,
subject string
) row format delimited fields terminated by ‘\t’ escaped by ‘\’ stored as textfile;

insert into test(name,subject)values(‘name1’,‘语文’);
insert into test(name,subject)values(‘name2’,‘数学,英语,语文’);

行转列后（分隔符为","）：

select name,t.col as subject from test lateral view explode(split(subject,',')) t as col

2.mysql 列转行实现方式

其中 join 辅助表 mysql.help_topic 是为了循环到每一个分隔符，截取出数据

select
a.name,
substring_index(substring_index(a.subject(要拆分的字段),',',b.help_topic_id+1),',',-1) 
from 
test(目标表) a
join
mysql.help_topic(数据库通用连续行号表) b
on b.help_topic_id < (length(a.subject(要拆分的字段)) - length(replace(a.subject,',',''))+1)
order by a.name;

2.sqlserver 列转行实现方式

使用xml的方式

select 
a.name,b.subject
from 
(select name,subject=convert(xml,'<root><v>'+replace(subject,',','</v><v>')+'</v></root>') from test) a
outer apply
(select subject=C.v.value('.','nvarchar(100)') from a.subject.nodes('/root/v')C(v)) b

二、行转列

1.hive 行转列实现方式

原始数据：
create table test(
name string,
subject string
) row format delimited fields terminated by ‘\t’ escaped by ‘\’ stored as textfile;

insert into test(name,subject) values(‘name1’,‘语文’);
insert into test(name,subject) values(‘name2’,‘数学’);
insert into test(name,subject) values(‘name2’,‘英语’);
insert into test(name,subject) values(‘name2’,‘语文’);

行转列后（按照","拼接）：

select
name,
concat_ws(',',collect_set(subject)) as subject
from test
group by name

注：如果不想去重，可以将 collect_set() 换成 collect_list()

2.mysql 行转列实现方式

SELECT
    name,
    GROUP_CONCAT(subject,",") AS subject
FROM
    test
GROUP BY
    name;

2.sqlserver 行转列实现方式

使用xml path实现

select
	name,
	subject = (stuff((select ',' + subject from test where name = a.name for xml path('')),1,1,''))
from
	test a
group by
	name