Stack Exchange
Hive Coalesce function isn't returning expected results after performing concat_ws and and collect_list
I need to pivot column servicename in table services and then coalesce values.
services:
userid servicename
1      A
2      B
2      C
3      B
1      C
4
5      C

The query I am trying is 
SELECT userid,
       COALESCE(a,b,c) AS servicename
FROM (SELECT userid,
             concat_ws('',a) AS a,
             concat_ws('',b) AS b,
             concat_ws('',c) AS c
      FROM (SELECT userid,
                   collect_list(a.group_map['A']) AS a,
                   collect_list(a.group_map['B']) AS b,
                   collect_list(a.group_map['C']) AS c
            FROM (SELECT userid,
                         MAP(servicename,servicename) AS group_map
                  FROM (SELECT userid, servicename, servicename FROM services) AS table1) AS table2) AS table3) AS table4

And here is the breakdown of each SELECT query with it's result:
Query 1:
SELECT userid,
       servicename,
       servicename
FROM services

Result 1:
userid servicename servicename
   1      A           A
   2      B           B
   2      C           C
   3      B           B
   1      C           C
   4 
   5      C           C 

Query 2:
SELECT userid,
       MAP(servicename,servicename) AS group_map
FROM table1

Result 2:
userid    group_map
    1      {"A":"A"}
    2      {"B":"B"}
    2      {"C":"C"}
    3      {"B":"B"}
    1      {"C":"C"}
    4      {"null":"null"}
    5      {"C":"C"}

Query 3:
SELECT userid,
       collect_list(a.group_map['A']) AS a,
       collect_list(a.group_map['B']) AS b,
       collect_list(a.group_map['C']) AS c
FROM table2

Result 3:
userid    a      b      c
1       ["A"]         ["C"]
2              ["B"]  ["C"]
3              ["B"]
4           
5                     ["C"]

The following concat_ws function is to convert array to String
Query 4:
SELECT userid,
       concat_ws('',a) AS a,
       concat_ws('',b) AS b,
       concat_ws('',c) AS c
FROM table3

Result 4:
userid    a    b   c
1         A        C
2              B   C
3              B
4           
5                  C

And in the end using COALESCE function in the query to get results as below:
Query 5
SELECT userid,
       COALESCE(a,b,c) AS servicename
FROM table4

And below is the result which I expect to see
Result 5:
userid  servicename
1       A
2       B
3       B
4
5       C

However, I get only Service A or null like below:
userid  servicename
    1       A
    2       
    3       
    4
    5       

Does anyone suspect any issue? Is there casting required? 
I have been trying different things and tried to search on web as well, but no luck yet. 
Thanks in advance!

Looks like you just want the 1 servicename for each id
select * from 
(
     select userid,servicename,row_number() over (partition by userid order by servicename) as row_no 
) res
where res.row_no = 1

This should get you
userid  servicename
1       A
2       B
3       B
4
5       C

If you are trying to pivot the servicename for each userid
select userid, collect_set(servicename)
from table
group by userid;

This should get you
userid  servicename
1       A,C
2       B,C
3       B
4
5       C


Yes I just need 1 service name, and in a preference, which is A, B, and C depending on whichever is first available else null. Thanks! This solution works!

Can I add multiple columns in partition by clause?

Yes you can, the order by in partition will ensure A,B,C.You can use whatever order you want,partition by multiple columns etc.The row number will be added based on your partition and order by clause.

How can I set order of service names? For eg. First it should take B, then A and then C.

Ah and it gets complicated.Since the order is arbitary,you will have to assign an order sequence and then use that as the basis for order by clause. For example.Create a new column serviceorder and assign A,B,C values 2,1,3 and then use it in orderby clause
