我对Oracle Sql有疑问，
如果我有一个8列的名为A的数据：
Spot| ID |Sunday|Monday|Tuesday|Wednesday|Thursday|Friday|Saturday ------------------------------------------------------------------------- A| 1 | 0.1 |0.15 | ........................................... ------------------------------------------------------------------------- A| 2 | 0.2 |0.2 | ........................................... ------------------------------------------------------------------------- A| 3 | 0.3 |0.25 | ........................................... ------------------------------------------------------------------------- A| 4 | 0.4 |0.4 | ........................................... -------------------------------------------------------------------------
我可以这样将其转换为表B：
Spot| Day of Week | ID | Value ------------------------------------------------------------------------- A| 1 | 1 | 0.1 ------------------------------------------------------------------------- A| 1 | 2 | 0.2 ------------------------------------------------------------------------- A| 1 | 3 | 0.3 ------------------------------------------------------------------------- A| 1 | 4 | 0.4 ------------------------------------------------------------------------- A| 2 | 1 | 0.15 ------------------------------------------------------------------------- .......................................................................
这是将列（周日至周六）合并为一个名为“星期几”的新列
我该怎么办？谢谢！
您可以使用UNPIVOT：
UNPIVOT
Oracle安装程序 ：
CREATE TABLE your_table ( spot, id, sunday, monday, tuesday, wednesday, thursday, friday, saturday ) AS SELECT 'A', 1, 0.1, 0.15, 0.2, 0.25, 0.3, 0.35, 0.4 FROM DUAL UNION ALL SELECT 'A', 2, 0.15, 0.2, 0.25, 0.3, 0.35, 0.4, 0.45 FROM DUAL UNION ALL SELECT 'A', 3, 0.2, 0.25, 0.3, 0.35, 0.4, 0.45, 0.5 FROM DUAL;
查询 ：
SELECT * FROM your_table UNPIVOT ( Value FOR Day_of_week IN ( sunday AS 1, monday AS 2, tuesday AS 3, wednesday AS 4, thursday AS 5, friday AS 6, saturday AS 7 ) );
输出 ：
S ID DAY_OF_WEEK VALUE - -- ----------- ----- A 1 1 .1 A 1 2 .15 A 1 3 .2 A 1 4 .25 A 1 5 .3 A 1 6 .35 A 1 7 .4 A 2 1 .15 A 2 2 .2 A 2 3 .25 A 2 4 .3 A 2 5 .35 A 2 6 .4 A 2 7 .45 A 3 1 .2 A 3 2 .25 A 3 3 .3 A 3 4 .35 A 3 5 .4 A 3 6 .45 A 3 7 .5
