sql – Oracle将多个列合并为一个

关于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
-------------------------------------------------------------------------
 .......................................................................

将列(星期日到星期六)组合到一个名为“星期几”的新列中

我该怎么办？谢谢！

解决方法

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

（编辑：厦门站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!