How can vertical data in Oracle be transformed into horizontal data?

2 years ago
Ava Mitchell
2 minutes

To convert vertical data in an Oracle database to horizontal data, you can utilize the PIVOT operator in Oracle.

Here is an example showing how to use the PIVOT operator to transform vertical data into horizontal data.

Suppose we have the following table structure:

CREATE TABLE sales (
    product_id INT,
    category VARCHAR2(50),
    sales_date DATE,
    sales_amount NUMBER
);

The data in the table is as follows:

INSERT INTO sales VALUES (1, 'Category A', '2021-01-01', 100);
INSERT INTO sales VALUES (2, 'Category B', '2021-01-01', 200);
INSERT INTO sales VALUES (1, 'Category A', '2021-02-01', 150);
INSERT INTO sales VALUES (2, 'Category B', '2021-02-01', 250);
INSERT INTO sales VALUES (1, 'Category A', '2021-03-01', 120);
INSERT INTO sales VALUES (2, 'Category B', '2021-03-01', 180);

To convert the above data into horizontal data, you can use the following query statement:

SELECT *
FROM (
    SELECT product_id, sales_date, sales_amount
    FROM sales
)
PIVOT (
    SUM(sales_amount)
    FOR sales_date IN (
        TO_DATE('2021-01-01', 'YYYY-MM-DD') AS "2021-01-01",
        TO_DATE('2021-02-01', 'YYYY-MM-DD') AS "2021-02-01",
        TO_DATE('2021-03-01', 'YYYY-MM-DD') AS "2021-03-01"
    )
)
ORDER BY product_id;

The results will be as follows:

PRODUCT_ID  2021-01-01  2021-02-01  2021-03-01
-----------------------------------------------
1           100         150         120
2           200         250         180

The query utilized the PIVOT operator to transform the values in the sales_date column into column names, while also calculating the total sales amount for each combination of product_id and sales_date.

Please note that the PIVOT operator is available in Oracle 11g and above. If you are using an older version, you may need to use a different approach.

bannerAds