Hive Group concat Alternative.sql

hive> create table t1 (x int, s string);
hive> insert into t1 values (1, "one"), (3, "three"), (2, "two"), (1, "one"),(1, "four"), (3, "five"), (2, "six"), (7, "seven");
hive> SELECT x,count(*) as num_of_rows, concat_ws(',' , collect_set(s)) as group_con from t1 group by x;
Query ID = impadmin_20180104103104_98e64351-d108-4c1b-97ec-ba5e5860aeec
Total jobs = 1
…
OK
x num_of_rows group_con
1    3                 one,four
2    2                 two,six
3    2                 three,five
7    1                 seven
Time taken: 3.844 seconds, Fetched: 4 row(s)
hive>