hive行转列/列转行

create table syc_ads.test_transform (
    name          string comment '姓名',
    constellation string comment '星座',
    blood         string comment '血型',
    hobby         string comment '爱好'
);

insert into table syc_ads.test_transform values('郭一','天蝎座','A','游戏,读书,滑板');
insert into table syc_ads.test_transform values('王二','白羊座','B','滑板,追剧');
insert into table syc_ads.test_transform values('孙三','白羊座','A','乒乓球,游戏');
insert into table syc_ads.test_transform values('李四','射手座','A','篮球,滑板');
insert into table syc_ads.test_transform values('赵五','白羊座','B','乒乓球,读书,滑板');
insert into table syc_ads.test_transform values('黄六','天蝎座','A','乒乓球,读书,滑板');

SELECT
    t1.a,
    CONCAT_WS("|",collect_set(t1.name))
FROM (
    SELECT
        name
        ,CONCAT_WS(',',constellation,blood) a
    FROM syc_ads.test_transform
)t1
GROUP BY t1.a

SELECT
    name,
    hobby1
FROM syc_ads.test_transform
lateral VIEW explode(split(hobby,",")) test_transform AS hobby1;