Group_Concat With Group Clause in Impala

The CONCAT() function joins the input strings or different column values and returns as a single string. However, if we need to concatenate rows of strings (rows from the same column) into a single string with a desired separator, this will not work.

There comes a function GROUP_CONCAT that fulfills this requirement and this function known with different names in various RDBMS and Big Data Technologies like Hive.

GROUP_CONCAT() is a function which merges the data from multiple rows into one field. It is a GROUP BY function which returns a string. Comma (,) or any user specified delimiter can be used to separate the values in the string.

To know more about this function and similar functionality in various RDBMS and big data technologies, please click here.

We’ll look at how to use the GROUP CONCAT function with the GROUP clause in this post. This is used to combine the categorized values into a single row for each category.

Sample Data:

CREATE TABLE GrpSubjects(id INT, Subject STRING, SubjectGroup STRING);

INSERT INTO GrpSubjects (Subject,SubjectGroup) VALUES
(‘Mathematics’,’MPC’),
(‘Mathematics’,’MEC’),
(‘Physics’,’MPC’),
(‘Chemistry’,’MPC’),
(‘Economics’,’MEC’),
(‘Economics’,’CEC’),
(‘Economics’,’HEC’),
(‘Commerce’,’MEC’),
(‘Commerce’,’CEC’),
(‘History’,’HEC’),
(‘Civics’,’CEC’),
(‘Civics’,’HEC’);

Now, let’s get the subjects in a single row for each subject-group.

SELECT SubjectGroup, GROUP_CONCAT(Subject)
FROM GrpSubjects
GROUP BY SubjectGroup;



Hope you find this article helpful.

Please follow us for more interesting updates.