hive的行转列的应用

有如下数据：

1,a
1,b
1,c
1,e
2,a
2,c
2,d
2,f
3,a
3,b
3,c
3,e

数据说明：

其中第一列为学生id，第二列为学生选的课程。

需求如下：

转成如下形式：其中1表示已经选修，0表示未选修。

id	a	b	c	d	e	f
1	1	1	1	0	1	0
2	1	0	1	1	0	1
3	1	1	1	0	1	0

建表以及导入数据：

CREATE TABLE t_course (
    id INT,
    course string
    ) row format delimited fields terminated BY ",";

LOAD data LOCAL inpath "/home/t_course.txt" INTO TABLE t_course;

查看数据

1. 使用collection_set函数来进行去重，并且合并为一个list，多行变一行；
2. 使用sort_array进行升序排序；
3. 使用group by按照id进行分组，多行变一行；
4. 最后利用笛卡尔积，将1中的所有数据和4中的数据进行join；
5. 使用array_contains来判断元素是否在集合中；

代码如下：

SELECT id,
    CASE 
        WHEN array_contains(course, all_course [0])
            THEN 1
        ELSE 0
        END AS a,
    CASE 
        WHEN array_contains(course, all_course [1])
            THEN 1
        ELSE 0
        END AS b,
    CASE 
        WHEN array_contains(course, all_course [2])
            THEN 1
        ELSE 0
        END AS c,
    CASE 
        WHEN array_contains(course, all_course [3])
            THEN 1
        ELSE 0
        END AS d,
    CASE 
        WHEN array_contains(course, all_course [4])
            THEN 1
        ELSE 0
        END AS e,
    CASE 
        WHEN array_contains(course, all_course [5])
            THEN 1
        ELSE 0
        END AS f
FROM (
    SELECT id,
        course,
        all_course
    FROM (
        SELECT id,
            collect_set(course) AS course
        FROM t_course
        GROUP BY id
        ) a
    JOIN (
        SELECT sort_array(collect_set(course)) AS all_course
        FROM t_course
        ) b
    ) c
ORDER BY id;

结果如下：

OK，现在输出的结果就是我们想要的了。

说明

1.不知大家有没有更加优雅的实现方式，若有，请指教。
2.受水平所限，若有不对之处，恳请指正，不胜感激。

最后感谢阅读！