Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Converting rows into dynamic colums in Hive


Copy link to this message
-
Re: Converting rows into dynamic colums in Hive
you should be able to do this in hive using a group by on alpha and then
using a combination of the max and if statement... something on the
following lines

select alpha, max(abc), max(pqr), ...
(
  select alpha, if (beta == 'abc', Gamma, NULL) as abc, if (beta == 'pqr',
Gamma, NUL) as pqr, ....
  from table
)
group by alpha

something on those lines...

Ashish

On Tue, Aug 7, 2012 at 1:57 PM, <[EMAIL PROTECTED]> wrote:

>  Hi All,****
>
> ** **
>
> One of my Query output looks like-****
>
> ** **
>
> *Alpha                    Beta                       Gamma*
>
> 123                         xyz                         1.0****
>
> 123                         abc                         0.5****
>
> 123                         pqr                         1.3****
>
> 123                         def                         2.1****
>
> *456                         xyz                         0.1*
>
> *456                         abc                         0.6*
>
> *456                         pqr                         1.9*
>
> *456                         def                         3.2*
>
> *456                         lmn                        1.1*
>
> *456                         sdf                          1.2*
>
> ** **
>
> I want the output for the data visualization purpose to look like
> (basically taking rows from one table and making it column in another table)
> ****
>
> ** **
>
> *Alpha                    xyz         abc         pqr         def
> lmn        sdf          *
>
> 123                         1.0          0.5          1.3
> 2.1                                          ****
>
> 456                         0.1          0.6          1.9
> 3.2          1.1          1.2****
>
> ** **
>
> Question – Can it be done in Hive? If not, any suggestions.****
>
> ** **
>
> Thanks,****
>
> Richin****
>
> ** **
>
> ** **
>