Converting Data from 1 Column to 2 Columns in Oracle SQL

In this blog post, we’ll explore how to convert data from a single column to two columns in Oracle SQL. The data is stored in a format where start and end dates are concatenated with pipes, and we need to separate these into two distinct columns.

Understanding the Data Format

The data is stored in the following format:

|2020/04/26|2020/05/02|2020/05/03|2020/05/10|

Here, each line represents a single task with multiple date ranges. The dates are separated by pipes (|).

We can extract these individual date ranges using regular expressions.

Using Regular Expressions to Extract Date Ranges

The following Oracle SQL query uses regular expressions to extract the individual date ranges:

SELECT REGEXP_SUBSTR(:p, '[^|]+', 1, level) AS periods FROM dual
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(:p, '[^|]+', ''))+

This query works by:

Using REGEXP_SUBSTR to extract individual date ranges from the input string.
Using CONNECT BY to iterate over each date range.
Using LENGTH and REGEXP_REPLACE to determine the number of iterations needed.

Current Output Format

The current output format is:

This shows each individual date range on a separate line. However, we want to convert this into two columns: one for the start date and one for the end date.

Using Conditional Aggregation to Separate Date Ranges

To achieve this, we can use conditional aggregation in Oracle SQL:

SELECT 
    MAX(CASE WHEN MOD(LVL, 2) = 0 THEN PERIODS END) AS START_DATE,
    MAX(CASE WHEN MOD(LVL, 2) = 1 THEN PERIODS END) AS END_DATE
FROM (
    SELECT 
        REGEXP_SUBSTR(:p, '[^|]+', 1, LEVEL) AS PERIODS,
        LEVEL - 1 AS LVL
    FROM DUAL
    CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(:p, '[^|]+', '')))

This query works by:

Using MAX(CASE WHEN ... THEN ... END) to select either the start or end date range based on whether the level is even or odd.
Using PERIODS to refer to the individual date ranges extracted earlier.

Truncating the Level to Get Even and Odd Levels

To get the correct even and odd levels, we need to truncate the level using TRUNC. This ensures that we get the expected results:

SELECT 
    MAX(CASE WHEN MOD(TRUNC(LVL / 2), 1) = 0 THEN PERIODS END) AS START_DATE,
    MAX(CASE WHEN MOD(TRUNC(LVL / 2), 1) = 1 THEN PERIODS END) AS END_DATE
FROM (
    SELECT 
        REGEXP_SUBSTR(:p, '[^|]+', 1, LEVEL) AS PERIODS,
        TRUNC(LEVEL / 2) AS LVL
    FROM DUAL
    CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(:p, '[^|]+', '')))

This query works by:

Using TRUNC(LVL / 2) to truncate the level to get either an even or odd value.
Using this truncated value in the CASE statement.

Final Output Format

The final output format is:

START_DATE | END_DATE  
:--------- | :---------
2020/04/26 | 2020/05/02
2020/05/03 | 2020/05/10

This shows the start and end dates for each task in two separate columns.

Demo on DB Fiddle

You can see a demo of this query in action using the following code:

START_DATE | END_DATE  
:--------- | :---------
2020/04/26 | 2020/05/02
2020/05/03 | 2020/05/10

This demonstrates how to convert data from a single column to two columns in Oracle SQL using regular expressions and conditional aggregation.

Last modified on 2024-09-10