How to get combined values from a table in hive

Question

Have a table in Hive with a following structure:

 col1 col2 col3 col4 col5 col6
 -----------------------------
 AA NM ER NER NER NER
 AA NM NER ERR NER NER
 AA NM NER NER TER NER
 AA NM NER NER NER ERY

Wrote a query to fetch the record from the table:

Select distinct(col1),col2, array(concat(
CASE WHEN col3=='ER' THEN 'ER' 
 WHEN col4=='ERR' THEN 'ERR'
 WHEN col5=='TER' THEN 'TER'
 WHEN col6=='ERY' THEN 'ERY'
ELSE 'NER' END

but its not working. Not getting how to go about it.

Expected O/P:

col1 col2 col3
--------------
AA NM ['ER','ERR','TER','ERY']

Any suggestion/hint will be really helpful.

• Iskuskov Alexander: WHEN col3=='ER' THEN 'ER' what does it mean? At least one value 'ER' contains in col3? Or something else?

Answer 1

Please try below -

select col1, col2, array(
max(CASE WHEN col3=='ER' THEN 'ER' else '' end),
max(CASE WHEN col4=='ERR' THEN 'ERR' else '' end),
max(CASE WHEN col5=='TER' THEN 'TER' else '' end), 
max(CASE WHEN col6=='ERY' THEN 'ERY' else '' end))
from table
group by col1, col2

Answer 2

You can obatin a string that seems an array using concat_ws

Select distinct(col1),col2,concat_ws('','[',
 concat_ws('', "'",col3,"',", "'",col4,"',","'",col5,"',","'",col6,"'"), 
 ']')
from my_table

Answer 3

This is a big complicated. I think that simply unpivoting is the simplest solution:

select col1, col2, collect_set(col)
from ((select col1, col2, col3 as col from t
 ) union -- intentional to remove duplicates
 (select col1, col2, col4 as col from t
 ) union -- intentional to remove duplicates
 (select col1, col2, col5 as col from t
 ) union -- intentional to remove duplicates
 (select col1, col2, col6 as col from t
 )
 ) t
where col is not null
group by col1, col2;