Oracle Combine several columns into one

Question

I have a question in regarding to Oracle Sql,

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

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'

How can I do that please? Thanks!

Answer 1

You can use UNPIVOT:

Oracle Setup:

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;

Query:

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
 ) );

Output:

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

Answer 2

There are essentially three ways to do this, each with their advantages and disadvantages. (There may be more than three, these are the ones I know.)

The first method uses UNION ALL and it is fine for small tables (performance is not important). It works in any version of Oracle. It goes something like this:

select spot, 1 as day_of_week, id, sunday  as value from a union all
select spot, 2               , id, monday           from a union all
.......

It's inefficient, because the base table (A) is read seven times, one time for each branch of UNION ALL. Some rows may be cached, so perhaps it's not totally I/O time multiplied by seven, but it's still inefficient.

Since Oracle 11.1 we have had PIVOT and UNPIVOT. You need unpivoting, like this:

select  spot, day_of_week, id, value
from    a
unpivot ( value for day_of_week in ( sunday as 1, monday as 2, .... ) )
;

This will work faster; it is not clear how Oracle implemented this under the hood, it may still do a UNION ALL, but a smart one - where the same row is used seven times, instead of being read repeatedly from disk. The disadvantage, of course, is that it is not available in older versions of Oracle.

The third method uses a Cartesian join. It will work fast and it is available in all versions.

select a.spot, h.day_of_week, id,
       case h.day_of_week when 1 then sunday
                          when 2 then monday
                          ..................
                          when 7 then saturday
       end  as value
from   a 
       cross join
       ( select level as day_of_week from dual connect by level <= 7) h
;

The subquery h in this solution may look fancy, but all it does is to create an inline view h with a single column, day_of_week, with the values from 1 to 7. It can be created in any other way, this is just a fast (and common) one.