Oracle将几列合并为一

Question

我对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
-------------------------------------------------------------------------
 .......................................................................

这是将列（周日至周六）合并为一个名为“星期几”的新列

我该怎么办？谢谢！

Answer 1

您可以使用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