Creating columns from results in Hive

Question

In hive I'm trying to take the data in a particular column and then split that up into new columns. Then I want to move a column that contains some values and move those into those new columns. If this isn't clear here's what I mean

Table Name: atable

Before:

Name Date Label Value

---

name1 date1 label1 1

---

name2 date2 label2 2

---

name3 date3 label3 3

---

name4 date4 label4 4

---

name5 date5 label5 5

After:

Name Date Label1 Label2 Label3 Label4 Label5

---

name1 date1 1

---

name2 date2************ 2

---

name3 date3********************* 3

---

name4 date4************************** 4

---

name5 date5****************************************** 5

---

Value 1 would be in column label 1. Value 2 would be in column label 2, etc... All the blanks would obviously be null.

Also if this is too hard or a bad idea, I was also thinking of putting them into separate tables. In each of these tables the columns would be name, date, and each of the values from the label column. Under that last column would be the value.

I have tried using dynamic partitioning and joining, but It doesn't seem to work Please help!! Thanks in advance.

Answer 1

Are you attempting to do a cross-tabulation? If so, see the PostgreSQL tablefunc contrib module, which offers the crosstab function. See the examples there.

To use this you need to be able to call native PostgreSQL functions via whatever Hive is.