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