Mailing-List: contact user-help@hive.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@hive.apache.org
Received-SPF: neutral (nike.apache.org: local policy)
MIME-Version: 1.0
In-Reply-To: 
 <2AD0F14A76338B4FA3C06F5BA1C1D2DB043E89E7@008-AM1MPN1-073.mgdnok.nokia.com>
References: 
 <2AD0F14A76338B4FA3C06F5BA1C1D2DB043E89E7@008-AM1MPN1-073.mgdnok.nokia.com>
Date: Tue, 7 Aug 2012 14:04:53 -0700
Message-ID: 
 <CAOn+50KY-Rm7-K+ntNxK7W3VfWFso09ACRfrMOUyri59Oomuvw@mail.gmail.com>
Subject: Re: Converting rows into dynamic colums in Hive
From: Ashish Thusoo <athusoo@qubole.com>
To: user@hive.apache.org
Content-Type: multipart/alternative; boundary=f46d042fddb081a7e504c6b358da

--f46d042fddb081a7e504c6b358da
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

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 =3D=3D 'abc', Gamma, NULL) as abc, if (beta =3D=3D=
 'pqr',
Gamma, NUL) as pqr, ....
  from table
)
group by alpha

something on those lines...

Ashish

On Tue, Aug 7, 2012 at 1:57 PM, <richin.jain@nokia.com> 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 tab=
le)
> ****
>
> ** **
>
> *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 =96 Can it be done in Hive? If not, any suggestions.****
>
> ** **
>
> Thanks,****
>
> Richin****
>
> ** **
>
> ** **
>

--f46d042fddb081a7e504c6b358da
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: base64

eW91IHNob3VsZCBiZSBhYmxlIHRvIGRvIHRoaXMgaW4gaGl2ZSB1c2luZyBhIGdyb3VwIGJ5IG9u
IGFscGhhIGFuZCB0aGVuIHVzaW5nIGEgY29tYmluYXRpb24gb2YgdGhlIG1heCBhbmQgaWYgc3Rh
dGVtZW50Li4uIHNvbWV0aGluZyBvbiB0aGUgZm9sbG93aW5nIGxpbmVzPGJyPjxicj5zZWxlY3Qg
YWxwaGEsIG1heChhYmMpLCBtYXgocHFyKSwgLi4uPGJyPig8YnI+oCBzZWxlY3QgYWxwaGEsIGlm
IChiZXRhID09ICYjMzk7YWJjJiMzOTssIEdhbW1hLCBOVUxMKSBhcyBhYmMsIGlmIChiZXRhID09
ICYjMzk7cHFyJiMzOTssIEdhbW1hLCBOVUwpIGFzIHBxciwgLi4uLjxicj4KoCBmcm9tIHRhYmxl
PGJyPik8YnI+Z3JvdXAgYnkgYWxwaGE8YnI+PGJyPnNvbWV0aGluZyBvbiB0aG9zZSBsaW5lcy4u
Ljxicj48YnI+QXNoaXNoPGJyPjxicj48ZGl2IGNsYXNzPSJnbWFpbF9xdW90ZSI+T24gVHVlLCBB
dWcgNywgMjAxMiBhdCAxOjU3IFBNLCAgPHNwYW4gZGlyPSJsdHIiPiZsdDs8YSBocmVmPSJtYWls
dG86cmljaGluLmphaW5Abm9raWEuY29tIiB0YXJnZXQ9Il9ibGFuayI+cmljaGluLmphaW5Abm9r
aWEuY29tPC9hPiZndDs8L3NwYW4+IHdyb3RlOjxicj4KPGJsb2NrcXVvdGUgY2xhc3M9ImdtYWls
X3F1b3RlIiBzdHlsZT0ibWFyZ2luOjAgMCAwIC44ZXg7Ym9yZGVyLWxlZnQ6MXB4ICNjY2Mgc29s
aWQ7cGFkZGluZy1sZWZ0OjFleCI+CgoKCgoKPGRpdiBsaW5rPSJibHVlIiB2bGluaz0icHVycGxl
IiBsYW5nPSJFTi1VUyI+CjxkaXY+CjxwIGNsYXNzPSJNc29Ob3JtYWwiPkhpIEFsbCw8dT48L3U+
PHU+PC91PjwvcD4KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PHU+PC91PqA8dT48L3U+PC9wPgo8cCBj
bGFzcz0iTXNvTm9ybWFsIj5PbmUgb2YgbXkgUXVlcnkgb3V0cHV0IGxvb2tzIGxpa2UtPHU+PC91
Pjx1PjwvdT48L3A+CjxwIGNsYXNzPSJNc29Ob3JtYWwiPjx1PjwvdT6gPHU+PC91PjwvcD4KPHAg
Y2xhc3M9Ik1zb05vcm1hbCI+PGI+QWxwaGGgoKCgoKCgoKCgoKCgoKCgoKCgIEJldGGgoKCgoKCg
oKCgoKCgoKCgoKCgoKCgIEdhbW1hPHU+PC91Pjx1PjwvdT48L2I+PC9wPgo8cCBjbGFzcz0iTXNv
Tm9ybWFsIj4xMjOgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKAgeHl6oKCgoKCgoKCgoKCgoKCgoKCg
oKCgoKCgIDEuMDx1PjwvdT48dT48L3U+PC9wPgo8cCBjbGFzcz0iTXNvTm9ybWFsIj4xMjOgoKCg
oKCgoKCgoKCgoKCgoKCgoKCgoKAgYWJjoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgIDAuNTx1Pjwv
dT48dT48L3U+PC9wPgo8cCBjbGFzcz0iTXNvTm9ybWFsIj4xMjOgoKCgoKCgoKCgoKCgoKCgoKCg
oKCgoKAgcHFyoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgIDEuMzx1PjwvdT48dT48L3U+PC9wPgo8
cCBjbGFzcz0iTXNvTm9ybWFsIj4xMjOgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKAgZGVmoKCgoKCg
oKCgoKCgoKCgoKCgoKCgoKCgIDIuMTx1PjwvdT48dT48L3U+PC9wPgo8cCBjbGFzcz0iTXNvTm9y
bWFsIj48Yj40NTagoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKAgeHl6oKCgoKCgoKCgoKCgoKCgoKCg
oKCgoKCgIDAuMTx1PjwvdT48dT48L3U+PC9iPjwvcD4KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PGI+
NDU2oKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgIGFiY6CgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoCAw
LjY8dT48L3U+PHU+PC91PjwvYj48L3A+CjxwIGNsYXNzPSJNc29Ob3JtYWwiPjxiPjQ1NqCgoKCg
oKCgoKCgoKCgoKCgoKCgoKCgoCBwcXKgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKAgMS45PHU+PC91
Pjx1PjwvdT48L2I+PC9wPgo8cCBjbGFzcz0iTXNvTm9ybWFsIj48Yj40NTagoKCgoKCgoKCgoKCg
oKCgoKCgoKCgoKAgZGVmoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgIDMuMjx1PjwvdT48dT48L3U+
PC9iPjwvcD4KPHAgY2xhc3M9Ik1zb05vcm1hbCI+PGI+NDU2oKCgoKCgoKCgoKCgoKCgoKCgoKCg
oKCgIGxtbqCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgIDEuMTx1PjwvdT48dT48L3U+PC9iPjwvcD4K
PHAgY2xhc3M9Ik1zb05vcm1hbCI+PGI+NDU2oKCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgIHNkZqCg
oKCgoKCgoKCgoKCgoKCgoKCgoKCgoKAgMS4yPHU+PC91Pjx1PjwvdT48L2I+PC9wPgo8cCBjbGFz
cz0iTXNvTm9ybWFsIj48dT48L3U+oDx1PjwvdT48L3A+CjxwIGNsYXNzPSJNc29Ob3JtYWwiPkkg
d2FudCB0aGUgb3V0cHV0IGZvciB0aGUgZGF0YSB2aXN1YWxpemF0aW9uIHB1cnBvc2UgdG8gbG9v
ayBsaWtlIChiYXNpY2FsbHkgdGFraW5nIHJvd3MgZnJvbSBvbmUgdGFibGUgYW5kIG1ha2luZyBp
dCBjb2x1bW4gaW4gYW5vdGhlciB0YWJsZSk8dT48L3U+PHU+PC91PjwvcD4KPHAgY2xhc3M9Ik1z
b05vcm1hbCI+PHU+PC91PqA8dT48L3U+PC9wPgo8cCBjbGFzcz0iTXNvTm9ybWFsIj48Yj5BbHBo
YaCgoKCgoKCgoKCgoKCgoKCgoKAgeHl6oKCgoKCgoKAgYWJjoKCgoKCgoKAgcHFyoKCgoKCgoKAg
ZGVmoKCgoKCgoKAgbG1uoKCgoKCgoCBzZGagoKCgoKCgoKAKPHU+PC91Pjx1PjwvdT48L2I+PC9w
Pgo8cCBjbGFzcz0iTXNvTm9ybWFsIj4xMjOgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoKAgMS4woKCg
oKCgoKCgIDAuNaCgoKCgoKCgoCAxLjOgoKCgoKCgoKAgMi4xoKCgoKCgoKCgIKCgoKCgoKCgoKCg
oKCgoCCgoKCgoKCgoKCgoKCgoKAKPHU+PC91Pjx1PjwvdT48L3A+CjxwIGNsYXNzPSJNc29Ob3Jt
YWwiPjQ1NqCgoKCgoKCgoKCgoKCgoKCgoKCgoKCgoCAwLjGgoKCgoKCgoKAgMC42oKCgoKCgoKCg
IDEuOaCgoKCgoKCgoCAzLjKgoKCgoKCgoKAgMS4xoKCgoKCgoKCgIDEuMjx1PjwvdT48dT48L3U+
PC9wPgo8cCBjbGFzcz0iTXNvTm9ybWFsIj48dT48L3U+oDx1PjwvdT48L3A+CjxwIGNsYXNzPSJN
c29Ob3JtYWwiPlF1ZXN0aW9uIJYgQ2FuIGl0IGJlIGRvbmUgaW4gSGl2ZT8gSWYgbm90LCBhbnkg
c3VnZ2VzdGlvbnMuPHU+PC91Pjx1PjwvdT48L3A+CjxwIGNsYXNzPSJNc29Ob3JtYWwiPjx1Pjwv
dT6gPHU+PC91PjwvcD4KPHAgY2xhc3M9Ik1zb05vcm1hbCI+VGhhbmtzLDx1PjwvdT48dT48L3U+
PC9wPgo8cCBjbGFzcz0iTXNvTm9ybWFsIj5SaWNoaW48dT48L3U+PHU+PC91PjwvcD4KPHAgY2xh
c3M9Ik1zb05vcm1hbCI+PHU+PC91PqA8dT48L3U+PC9wPgo8cCBjbGFzcz0iTXNvTm9ybWFsIj48
dT48L3U+oDx1PjwvdT48L3A+CjwvZGl2Pgo8L2Rpdj4KCjwvYmxvY2txdW90ZT48L2Rpdj48YnI+
Cg==
--f46d042fddb081a7e504c6b358da--