0

So I am pretty new to hive/sql, trying to figure out how to transform this data. I have data that looks something like this : raw_table

Owner | Team | Success  | Fail  |  
---------------------------------
C1    |  T1  |   90%    |  5%   |
C1    |  T2  |   70%    |  15%  |  
C1    |  T3  |   10%    |  55%  |

What I want to turn it into is 

Owner | T1_Success | T1_Fail | T2_Success| T2_Fail | T3_Success| T3_Fail 
------------------------------------------------------------------------
C1    | 90%        |   5%    |  70%      | 15%     |   10%     | 55%

To me, it makes more sense to group all the stats about the owner this way.

However, the number of teams is dynamic so what is typically recommended for static pivot is not applicable here I think (Actually I am not even sure if I should use pivot)

So I was wondering what is the simplest way to achieve this type of a table.

Since I am trying to do this in hive, was wondering if there is an easy way of doing this

ExceptionHandler
  • 213
  • 1
  • 8
  • 24
  • Just want to clarify T1, T2, T3 are all unique values with no repetitions – ExceptionHandler Jun 18 '19 at 09:58
  • The dynamic number of teams is why 1:many relationships are like this. To store it in your format places a hard-coded limit (and floor) on the number of children. For the solution you are looking for, what would that limit be? – Mike Jun 18 '19 at 10:00
  • The duplicate question has answers for both static and dynamic pivoting. – Shadow Jun 18 '19 at 10:00
  • Actually my bad. I realized I needed help in hive query and not really mysql. – ExceptionHandler Jun 18 '19 at 10:10

0 Answers0