Menu
Is there any way to combine/concat the fields within one column by grouping them. Eg:
col1 col2
1 aa
1 bb
1 cc
2 dd
2 ee
I want to query something like :
select col1, concat(col2) from tableName group by col1;
Output should be :
1 aa,bb,cc
2 dd,ee
Is there any function in hive to do this ?
725
You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.
To concatenate strings in MySQL with GROUP BY, you need to use GROUP_CONCAT() with a SEPARATOR parameter which may be comma(') or space (' ') etc.
We can perform the above activity using the CONCAT() function. CONCAT(): It takes column names as parameters and returns a column with value after concatenating all the values of the column passed parameters to the function.
you can use concat_ws() and collect_list() to achieve this....
Something like
select id , concat_ws(",", collect_list(val)) from test group by id;
"," is the seperator in the above query.
151
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
