CODEWITHSUNDEEP
sqlhivehiveql
Bhaskar
Bhaskar

Reputation: 51

How to concat columns with 2 delimiters in hive

How to concat columns with 2 delimiters in hive. Please find below the input columns.

column1 column2 column3 column4 column5 column6 column7 column8
1        abc    dfe     ghi     jkl     mno     pqr     1
2        abc    dfe     ghi     jkl     mno     pqr     1

Required output concatenated string grouped by colum8 i.e., value 1.

1:abc:dfe:ghi:jkl-mno:pqr,2:abc:dfe:ghi:jkl-mno:pqr

can someone help me.

Upvotes: 0

Views: 482

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

I would write this as:

select concat_ws(',',
                 collect_list(concat_ws(':', column1, column2, column3, column4, column5 || '-' || column6, column7)
                             )
                ) as output
from your_table
group by column8;

Upvotes: 1

Agung Sriwongo
Agung Sriwongo

Reputation: 133

Can you try this?
First, create new columns concatenating each columns for every row then collect the concantenated columns (using collect_list) into one column by grouping them based on column8 then concatenate it (using concat_ws): 

select concat_ws(',',
                 collect_list(concat(column1, ':', column2, ':', column3, ':', column4,':', column5, '-', column6, ':', column7
                                    )
                             )
                ) as output
from your_table
group by column8;

Upvotes: 1

Related Questions