This is the table I have:

``` | ID   |   Code   |  Proc1   |   Proc2 |
| 1    |    A     |   p      |   e     |
| 2    |    B     |   q      |   f     |
| 3    |    B     |   p      |   f     |
| 3    |    B     |   q      |   h     |
| 3    |    B     |   r      |   j     |
| 3    |    C     |   t      |   k     |
```

Here Proc1 can have any number of values. ID, Code & Proc1 together form a unique key for this table. I want to Pivot/ transpose this table so that each unique value in Proc1 becomes a new column, and corresponding value from Proc2 is the value in that column for the corresponding row. In essense, I'm trying to get something like:

``` | ID   |   Code   |  p   |   q |  r  |   t |
| 1    |    A     |   e  |     |     |     |
| 2    |    B     |      |   f |     |     |
| 3    |    B     |   f  |   h |  j  |     |
| 3    |    C     |      |     |     |  k  |
```

In the new transformed table, ID and code are the only primary key. From the ticket I mentioned above, I could get this far using the to_map UDAF. (Disclaimer - this may not be a step in the right direction, but just mentioning here, if it is)

``` | ID   |   Code   |  Map_Aggregation   |
| 1    |    A     |   {p:e}            |
| 2    |    B     |   {q:f}            |
| 3    |    B     |   {p:f, q:h, r:j } |
| 3    |    C     |   {t:k}            |
```

But don't know how to get from this step to the pivot/transposed table I want. Any help on how to proceed?

Sep 28, 2018 19,247 views

## 5 answers to this question.

+1 vote

Here is the approach i used to solved this problem using hive's internal UDF function, "map":

```select
b.id,
b.code,
concat_ws('',b.p) as p,
concat_ws('',b.q) as q,
concat_ws('',b.r) as r,
concat_ws('',b.t) as t
from
(
select id, code,
collect_list(a.group_map['p']) as p,
collect_list(a.group_map['q']) as q,
collect_list(a.group_map['r']) as r,
collect_list(a.group_map['t']) as t
from (
select
id,
code,
map(proc1,proc2) as group_map
from
test_sample
) a
group by
a.id,
a.code
) b;
```

"concat_ws" and "map" are hive udf and "collect_list" is a hive udaf.

• 26,740 points
+1 vote

Here is the solution I ended up using:

```add jar brickhouse-0.7.0-SNAPSHOT.jar;
CREATE TEMPORARY FUNCTION collect AS 'brickhouse.udf.collect.CollectUDAF';

select
id,
code,
group_map['p'] as p,
group_map['q'] as q,
group_map['r'] as r,
group_map['t'] as t
from ( select
id, code,
collect(proc1,proc2) as group_map
from test_sample
group by id, code
) gm;
```

The to_map UDF was used from the brickhouse repo: https://github.com/klout/brickhouse

answered Oct 12, 2018 by Sunny
+1 vote

Yet another solution.

Pivot using Hivemall to_map function.

```SELECT
uid,
kv['c1'] AS c1,
kv['c2'] AS c2,
kv['c3'] AS c3
FROM (
SELECT uid, to_map(key, value) kv
FROM vtable
GROUP BY uid
) t
```

uid c1 c2 c3 101 11 12 13 102 21 22 23

Unpivot

```SELECT t1.uid, t2.key, t2.value
FROM htable t1
LATERAL VIEW explode (map(
'c1', c1,
'c2', c2,
'c3', c3
)) t2 as key, value
```

uid key value 101 c1 11 101 c2 12 101 c3 13 102 c1 21 102 c2 22 102 c3 23

answered Oct 12, 2018 by Sushant
+1 vote

For Unpivot, we can simply use below logic.

```SELECT Cost.Code, Cost.Product, Cost.Size
, Cost.State_code, Cost.Promo_date, Cost.Cost, Sales.Price
FROM
(Select Code, Product, Size, State_code, Promo_date, Price as Cost
FROM Product
Where Description = 'Cost') Cost
JOIN
(Select Code, Product, Size, State_code, Promo_date, Price as Price
FROM Product
Where Description = 'Sales') Sales
on (Cost.Code = Sales.Code
and Cost.Promo_date = Sales.Promo_date);```
answered Oct 12, 2018 by Anuj
+1 vote

Below is also a way for Pivot

```SELECT TM1_Code, Product, Size, State_code, Description
, Promo_date
, Price
FROM (
SELECT TM1_Code, Product, Size, State_code, Description
, MAP('FY2018Jan', FY2018Jan, 'FY2018Feb', FY2018Feb, 'FY2018Mar', FY2018Mar, 'FY2018Apr', FY2018Apr
,'FY2018May', FY2018May, 'FY2018Jun', FY2018Jun, 'FY2018Jul', FY2018Jul, 'FY2018Aug', FY2018Aug
,'FY2018Sep', FY2018Sep, 'FY2018Oct', FY2018Oct, 'FY2018Nov', FY2018Nov, 'FY2018Dec', FY2018Dec) AS tmp_column
FROM CS_ME_Spirits_30012018) TmpTbl
LATERAL VIEW EXPLODE(tmp_column) exptbl AS Promo_date, Price;```
answered Oct 12, 2018 by Rahul

## Hadoop Hive: How to insert data in Hive table?

First, copy data into HDFS. Then create ...READ MORE

## How Impala is fast compared to Hive in terms of query response?

Impala provides faster response as it uses MPP(massively ...READ MORE

## How to retrieve the list of sql (Hive QL) commands that has been executed in a hadoop cluster?

Well, what you can do is use ...READ MORE

+1 vote

## Hadoop Mapreduce word count Program

Firstly you need to understand the concept ...READ MORE

Hi, You can create one directory in HDFS ...READ MORE

–1 vote

## Is there a way to copy data from one one Hadoop distributed file system(HDFS) to another HDFS?

The distributed copy command, distcp, is a ...READ MORE