在sql中将列转换为行

8zzbczxx 于 2021-06-26 发布在 Hive

关注(0)|答案(1)|浏览(380)

我在其中一个实现中有一个场景要在hive上完成，以便进行报告。我有一个表结构，目前看起来如下-

+------+------+----------+----------+-------+-------+--------+--------+
| 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 |
+------+------+----------+----------+-------+-------+--------+--------+

表中的列需要转换为如下所示-

+------+------+-------+----+-----+
| 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 |
+------+------+-------+----+-----+

如何通过sql实现这一点。我正在使用hive作为我的数据存储。非常感谢您的帮助

sql Hive transformation

来源：https://stackoverflow.com/questions/49023657/transforming-columns-to-rows-in-sql

1条答案

按热度按时间

b1uwtaje1#

你可以用这个：

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;

赞(0）回复(0）举报 2021-06-26

我来回答

在sql中将列转换为行

1条答案

相关问题

热门标签

最新问答