关于Oracle sql,我有一个问题,
如果我有一个名为A的数据,有8列：
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：
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.4,0.45 FROM DUAL UNION ALL SELECT 'A',3,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
