CODEWITHSUNDEEP
sqlhivetransformation
nehalkum
nehalkum

Reputation: 11

Transforming Columns to Rows in SQL

I have a scenario in one of the implementations to be done on Hive for reporting. I have a table structure that currently looks as below - 

+------+------+----------+----------+-------+-------+--------+--------+
| Col1 | Col2 | M1_Today | M2_Today | M1_LW | M2_LW | M1_L2W | M2_L2W |
+------+------+----------+----------+-------+-------+--------+--------+
| A    | A1   |       10 |      200 |     9 |   190 |     11 |    210 |
| A    | A2   |       12 |      210 |    11 |   200 |     13 |    220 |
| B    | B1   |       15 |      300 |    14 |   290 |     16 |    310 |
| B    | B2   |       18 |      310 |    17 |   300 |     19 |    320 |
+------+------+----------+----------+-------+-------+--------+--------+

The columns in the table need to be transformed to appear as below - 

+------+------+-------+----+-----+
| Col1 | Col2 | Col3  | M1 | M2  |
+------+------+-------+----+-----+
| A    | A1   | Today | 10 | 200 |
| A    | A1   | LW    |  9 | 190 |
| A    | A1   | L2W   | 11 | 210 |
| A    | A2   | Today | 12 | 210 |
| A    | A2   | LW    | 11 | 200 |
| A    | A2   | L2W   | 13 | 220 |
| B    | B1   | Today | 15 | 300 |
| B    | B1   | LW    | 16 | 310 |
| B    | B1   | L2W   | 14 | 290 |
| B    | B2   | Today | 18 | 310 |
| B    | B2   | LW    | 17 | 300 |
| B    | B2   | L2W   | 19 | 320 |
+------+------+-------+----+-----+

How can this be achieved via SQL. I am using HIVE as my datastore. Any help is much appreciated

Upvotes: 1

Views: 55

Answers (1)

Pham X. Bach
Pham X. Bach

Reputation: 5432

You could use this:

SELECT Col1, Col2, 'Today' AS Col3 , M1_Today AS M1, M2_Today AS M2 
FROM table_name
UNION ALL
SELECT Col1, Col2, 'LW' AS Col3 , M1_LW AS M1, M2_LW AS M2 
FROM table_name 
UNION ALL
SELECT Col1, Col2, 'L2W' AS Col3 , M1_L2W AS M1, M2_L2W AS M2 
FROM table_name
ORDER BY Col1, Col2, Col3 DESC;

Upvotes: 1

Related Questions