Oracle UNPIVOT 函数用于将多维数据转换为单维数据
假设我们有一个名为 sales_data 的表,其中包含以下列:year, region, product, sales。我们想要使用 UNPIVOT 函数将 sales_data 表转换为以下格式:year, region, product, month, sales。
您可以按照以下步骤操作:
CREATE TABLE sales_data (
year NUMBER,
region VARCHAR2(50),
product VARCHAR2(50),
jan NUMBER,
feb NUMBER,
mar NUMBER,
apr NUMBER,
may NUMBER,
jun NUMBER,
jul NUMBER,
aug NUMBER,
sep NUMBER,
oct NUMBER,
nov NUMBER,
dec NUMBER
);
INSERT INTO sales_data VALUES (2021, 'North America', 'Product A', 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000, 1100, 1200);
INSERT INTO sales_data VALUES (2021, 'Europe', 'Product B', 200, 300, 400, 500, 600, 700, 800, 900, 1000, 1100, 1200, 1300);
SELECT *
FROM sales_data
UNPIVOT (
sales FOR month IN (
jan AS 'Jan', feb AS 'Feb', mar AS 'Mar', apr AS 'Apr',
may AS 'May', jun AS 'Jun', jul AS 'Jul', aug AS 'Aug',
sep AS 'Sep', oct AS 'Oct', nov AS 'Nov', dec AS 'Dec'
)
);
这将返回以下结果:
YEAR REGION PRODUCT MONTH SALES
---- --------------- ------- ----- -----
2021 North America Product A Jan 100
2021 North America Product A Feb 200
2021 North America Product A Mar 300
...
2021 Europe Product B Jan 200
2021 Europe Product B Feb 300
2021 Europe Product B Mar 400
...
现在,您已经成功地使用 Oracle UNPIVOT 函数处理了多维数据。