Pivot函数在Oracle数据库中用于将行数据转换为列数据,从而使数据更易于分析和查看
示例:
SELECT *
FROM sales_data
PIVOT (
SUM(sales) AS total_sales,
AVG(profit) AS avg_profit
FOR product_id IN (1001 AS 'Product A', 1002 AS 'Product B')
);
示例:
DECLARE
v_cols CLOB;
BEGIN
SELECT LISTAGG('''' || product_name || ''' AS "' || product_name || '"', ', ') WITHIN GROUP (ORDER BY product_name)
INTO v_cols
FROM (SELECT DISTINCT product_name FROM sales_data);
EXECUTE IMMEDIATE '
SELECT *
FROM sales_data
PIVOT (
SUM(sales) AS total_sales,
AVG(profit) AS avg_profit
FOR product_name IN (' || v_cols || ')
)';
END;
/
示例:
SELECT *
FROM sales_data
PIVOT JSON (
SUM(sales) AS total_sales,
AVG(profit) AS avg_profit
FOR product_id IN (1001 AS 'Product A', 1002 AS 'Product B')
);
请注意,上述示例中的SQL语法可能因您使用的Oracle数据库版本而有所不同。在使用这些新特性之前,请确保您的数据库版本支持它们。