Thanks a lot Dudu. Could you also tell how can I use concat with group by clause in have. I have n rows with col1, col2, col3 and i want a result grouped by col1 and concat all values of col2 and col3.
Id,key,value, value2 ______________________ 1,fname,Dudu, m1 1,lname,Markowitz, m2 2,fname, Andrew, m3 2,lname, Sears,m4 And I need result like below Id, appended (group by key) ______________________ Fname , Dudu | m1 | Andrew | m3 Lname, Markowitz| m2 | Sears | m4 Thanks a lot for your help. On Saturday, April 23, 2016, Markovitz, Dudu <[email protected]> wrote: > Another example (with first name and last name), same principal > > > > Dudu > > > > > > Given the following table: > > > > id, first_name,last_name > > ______________________ > > 1,Dudu,Markovitz > > 2,Andrew,Sears > > > > select id,key,value from my_table lateral view explode > (map('fname',first_name,'lname',last_name)) t; > > > > The result will look like: > > > > Id,key,value > > ______________________ > > 1,fname,Dudu > > 1,lname,Markovitz > > 2,fname, Andrew > > 2,lname, Sears > > > > > > *From:* Deepak Khandelwal [mailto:[email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');>] > *Sent:* Saturday, April 23, 2016 9:04 AM > *To:* [email protected] > <javascript:_e(%7B%7D,'cvml','[email protected]');> > *Subject:* Hive query to split one row into many rows such that Row 1 > will have col 1 Name, col 1 Value and Row 2 will have col 2 Name and col 2 > value > > > > Hi All, > > > > I am new to Hive and I am trying to create a query for below aituation. > Would appreciate if someone could guide on same. Thans a lot in advance. > > > > I have two TABLES shown below > > > > TABLE1 (USER_dETAILS) > > **USER_ID** | **USER_NAME** | **USER_ADDRESS** > > ------------+------------------+---------------- > > 1 USER1 ADDRESS111 > > 2 USER2 ADDRESS222 > > > > TABLE2 (USER_PARAMETERS) > > **USER_ID** | **PARAM_NAME** | **PARAM_VALUE** > > ------------+------------------+------------------ > > 1 USER_NAME USER1 > > 1 USER_ADDRESS ADDRESS111 > > 2 USER_NAME USER2 > > 2 USER_ADDRESS ADDRESS222 > > > > I need to insert data in table2(USER_PARAMETERS) FROM table1(USER_DETAILS) > in the format shown above. I can do this using UNION ALL but I want to > avoid it as there are like 10 such columns that i need to split like above. > > > > Can someone suggest a efficient hive query so that i can achieve the > results shown in table 2 from data in table 1 (Hive query to split one row > of data into multiple rows like such that Row 1 will have column1 Name, > column1 Value and Row 2 will have column 2 Name and column 2 value...). > > > > Thanks a lot > > Deepak > > >
