Oracle将几列合并为一 [英] Oracle Combine several columns into one

问题描述

关于Oracle Sql，我有一个疑问，

I have a question in regarding to Oracle Sql,

如果我有8列的名为A的数据:

If I have a data called A, with 8 columns:

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:

Can I convert it to a table B like this:

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

这是将列(星期日至星期六)合并到一个名为星期几"的新列

Which is to combine columns (Sunday through Saturday) to a new column called 'Day of Week'

我该怎么做?谢谢！

推荐答案

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

这篇关于Oracle将几列合并为一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！