thanks I may have missed something. Deepak might clarify. cheers
Dr Mich Talebzadeh LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* http://talebzadehmich.wordpress.com On 23 April 2016 at 08:07, Markovitz, Dudu <dmarkov...@paypal.com> wrote: > Hi Mich, it seems the request was for unpivot. > > > > Dudu > > > > *From:* Mich Talebzadeh [mailto:mich.talebza...@gmail.com] > *Sent:* Saturday, April 23, 2016 10:04 AM > *To:* user <user@hive.apache.org> > *Subject:* Re: 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 > > > > try this > > > > -- populate table user_parameters with user_id values (unique)from > user_details > > INSERT user_parameters > > SELECT user_id, null, null FROM user_details > > > > -- Update remaining columnsd > > UPDATE user_parameters > SET > > param_name = t1.user_name > > param_value = t1.user_address > FROM > > user_parameters t2 JOIN user_details t1 > ON t2.user_id = t1.user_id; > > > > > > Dr Mich Talebzadeh > > > > LinkedIn > *https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw > <https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>* > > > > http://talebzadehmich.wordpress.com > > > > > > On 23 April 2016 at 07:04, Deepak Khandelwal <dkhandelwal....@gmail.com> > wrote: > > 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 > > > > >
