0

Given following table1

id  category    city    value
100 A   x   10
100 A   y   20
100 B   x   1000
101 A   x   100
101 C   x   2
102 B   x   3

We want to transpose the values of category_city as columns, i.e.

id  A_x A_y B_x B_y C_x
100 10  20  1000    NULL    NULL
101 100 NULL    NULL    NULL    2
102 NULL    NULL    3   NULL    NULL

The "max(case when ...)" method mentioned in this thread doesn't works well. Say, even if we have only 100 distinct values of category and 10 distinct values of city, the combination of category_city will be 1000. We need to manually write 1000 lines of "case(when ...) as category_city". This is ugly.

SELECT t.userid
     MAX(CASE WHEN t.fieldname = 'Username' THEN t.fieldvalue ELSE NULL END) AS Username,
     MAX(CASE WHEN t.fieldname = 'Password' THEN t.fieldvalue ELSE NULL END) AS Password,
     MAX(CASE WHEN t.fieldname = 'Email Address' THEN t.fieldvalue ELSE NULL END) AS Email
FROM TABLE t 
GROUP BY t.userid

Any beautiful way to do this?

Community
  • 1
  • 1
user3007888
  • 25
  • 4
  • 1
    Possible duplicate of [How to transpose/pivot data in hive?](http://stackoverflow.com/questions/23025380/how-to-transpose-pivot-data-in-hive) – Dennis Jaheruddin Aug 13 '16 at 07:22
  • Here is a [Quora](https://www.quora.com/Is-there-a-way-to-transpose-data-in-Hive) post related to your question. – o-90 Aug 15 '16 at 14:40

0 Answers0