In Oracle databases, row-to-column conversion usually involves converting multiple rows of data in a table into columns, which is very common in data reporting and analysis. Oracle databases do not have direct functions or commands that can directly complete this transformation, but it can be implemented in several different ways, including usingCASEStatement,PIVOTOperations and dynamic SQL. Here are some ways to implement row-to-column conversion:
1. Row and Row
1. UseCASEStatement
We first check the total salary data for each position in each department
SELECT job,deptno,SUM(sal) AS sum_sal
FROM scott.emp
WHERE deptno IS NOT NULL
GROUP BY job,deptno
ORDER BY job, deptno;
JOB DEPTNO SUM_SAL
--------- ------ ----------
ANALYST 20 6000
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
PRESIDENT 10 5000
SALESMAN 30 5600
CASEThe statement can be inSELECTConvert row data into columns according to the conditions in the query. This approach is very effective when dealing with a fixed number of columns.
SELECT job,
SUM(CASE deptno WHEN 10 THEN sal ELSE 0 END) deptno_10_sal,
SUM(CASE deptno WHEN 20 THEN sal ELSE 0 END) deptno_20_sal,
SUM(CASE deptno WHEN 30 THEN sal ELSE 0 END) deptno_30_sal
FROM scott.emp
GROUP BY job;
-- Execute result set
JOB DEPTNO_10_SAL DEPTNO_20_SAL DEPTNO_30_SAL
--------- ------------- ------------- -------------
CLERK 1300 1900 950
SALESMAN 0 0 5600
PRESIDENT 5000 0 0
MANAGER 2450 2975 2850
ANALYST 0 6000 0
2. UsePIVOToperate
Oracle 11g and later versions have been introducedPIVOTOperator, specifically used to convert row data into column data. It makes the conversion process more concise and intuitive.
The syntax of the PIVOT function is
SELECT ...
FROM ...
PIVOT
( pivot_clause
pivot_for_clause
pivot_in_clause )
WHERE ...
in:
- pivot_clause means aggregation operation on a certain column
- pivot_for_clause means grouped and columns that need to be converted into rows
- pivot_in_clause represents the value range of column name for row-to-column
The PIVOT statement is between the FROM clause and the WHERE clause. We can filter the result set after row-to-column conversion through the WHERE clause, as follows:
WITH cte_data AS (
SELECT job, deptno, sal
FROM scott.emp
WHERE deptno IS NOT NULL
)
SELECT *
FROM cte_data
PIVOT (
SUM(sal) -- pivot_clause
FOR deptno -- pivot_for_clause
IN (10,20,30) -- pivot_in_clause
);
-- Execute result set
JOB 10 20 30
--------- ---------- ---------- ----------
CLERK 1300 1900 950
SALESMAN 5600
PRESIDENT 5000
MANAGER 2450 2975 2850
ANALYST 6000
From the above output, we can see that the column names of rows and columns are all numbers. We can specify an alias for these column names and specify where conditions are as follows:
WITH cte_data AS (
SELECT job, deptno, sal
FROM scott.emp
WHERE deptno IS NOT NULL
)
SELECT *
FROM cte_data
PIVOT (
SUM(sal) -- Sum and aggregate sal
FOR deptno -- Deptno needs to be row-to-row-redirected
IN (10 AS DEPTNO_10_sal,
20 AS DEPTNO_20_sal,
30 AS DEPTNO_30_sal) -- The range of column names after row-to-column conversion
)
WHERE job IN ('ANALYST','CLERK','SALESMAN');
-- Execute result set
JOB DEPTNO_10_SAL DEPTNO_20_SAL DEPTNO_30_SAL
--------- ------------- ------------- -------------
CLERK 1300 1900 950
SALESMAN 5600
ANALYST 6000
Notice:PIVOTIn-houseINThe clause lists all possible things you want to convert to a columndeptnovalue.
During aggregation, we can add multiple aggregate functions, as follows
WITH cte_data AS (
SELECT job, deptno, sal
FROM scott.emp
WHERE deptno IS NOT NULL
)
SELECT *
FROM cte_data
PIVOT (
SUM(sal) AS sum_sal, -- Sum and aggregate sal
COUNT(sal) AS cnt -- Aggregate the total number of sals
FOR deptno -- Deptno needs to be row-to-row-redirected
IN (10 AS DEPTNO_10_sal,
20 AS DEPTNO_20_sal,
30 AS DEPTNO_30_sal) -- The range of column names after row-to-column conversion
)
WHERE job IN ('ANALYST','CLERK','SALESMAN');
-- Execute result set
JOB DEPTNO_10_SAL_SUM_SAL DEPTNO_10_SAL_CNT DEPTNO_20_SAL_SUM_SAL DEPTNO_20_SAL_CNT DEPTNO_30_SAL_SUM_SAL DEPTNO_30_SAL_CNT
--------- --------------------- ----------------- --------------------- ----------------- --------------------- -----------------
CLERK 1300 1 1900 2 950 1
SALESMAN 0 0 5600 4
ANALYST 0 6000 2 0
pivot_for_clause can also use multiple fields, as follows:
WITH cte_data AS (
SELECT job, deptno, sal
FROM scott.emp
WHERE deptno IS NOT NULL
)
SELECT *
FROM cte_data
PIVOT (
SUM(sal) AS sum, -- Sum and aggregate sal
COUNT(sal) AS cnt -- Aggregate the total number of sals
FOR (deptno, job) -- Deptno and job need to be row-to-row-redirected
IN ((30, 'ANALYST') AS deptno_30_analyst,
(30, 'CLERK') AS deptno_30_clerk,
(30, 'SALESMAN') AS deptno_30_salesman) -- The range of column names after row-to-column conversion
);
-- Execute result set
DEPTNO_30_ANALYST_SUM DEPTNO_30_ANALYST_CNT DEPTNO_30_CLERK_SUM DEPTNO_30_CLERK_CNT DEPTNO_30_SALESMAN_SUM DEPTNO_30_SALESMAN_CNT
--------------------- --------------------- ------------------- ------------------- ---------------------- ----------------------
0 950 1 5600 4
3. Dynamic SQL
Dynamic SQL can be used to build and execute when the number or name of the column is unknown at the time of query execution (e.g., based on dynamic changes in data in the table).PIVOTQuery orCASEStatement. This usually involves using PL/SQL to write a program that can dynamically build and execute SQL statements.
in conclusion
Which method to choose depends on your specific needs, such as whether the number of columns is fixed, whether the columns need to be dynamically determined at runtime, etc. For simple fixed column conversion,CASEstatement orPIVOTOperation may be enough. For more complex dynamic column transformations, you may need to use dynamic SQL.
2. Column and turn rows
The column conversion operation in Oracle database, that is, converting column data in a table into row data, is a common data processing requirement. Here are some ways to implement Oracle column conversion:
Prepare test tables and data
CREATE table scott.t_pivoted_emp
AS
SELECT * FROM (
WITH cte_data AS (
SELECT job, deptno, sal
FROM scott.emp
WHERE deptno IS NOT NULL
)
SELECT *
FROM cte_data
PIVOT (
SUM(sal) sum
FOR deptno
IN (10 AS deptno_10_sal,
20 AS deptno_20_sal,
30 AS deptno_30_sal)
)
);
SQL> select * from scott.t_pivoted_emp;
JOB DEPTNO_10_SAL_SUM DEPTNO_20_SAL_SUM DEPTNO_30_SAL_SUM
--------- ----------------- ----------------- -----------------
CLERK 1300 1900 950
SALESMAN 5600
PRESIDENT 5000
MANAGER 2450 2975 2850
ANALYST 6000
1. Use CASE statements and UNION ALL
When the UNPIVOT function does not meet the requirements, you can use the CASE statement combined with UNION ALL to achieve more complex column conversion. Although this method is more cumbersome, it is more flexible.
SELECT * FROM (
SELECT job, 10 as deptno,DEPTNO_10_SAL_SUM as salary FROM scott.t_pivoted_emp
UNION
SELECT job, 20 as deptno,DEPTNO_20_sal_sum as salary FROM scott.t_pivoted_emp
UNION
SELECT job, 30 as deptno,DEPTNO_30_sal_sum as salary FROM scott.t_pivoted_emp
)
WHERE salary IS NOT NULL
ORDER BY job, deptno;
JOB DEPTNO SALARY
--------- ---------- ----------
ANALYST 20 6000
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
PRESIDENT 10 5000
SALESMAN 30 5600
9 rows selected
This method generates a row collection for each column, which contains the name and value of the original column.
2. Use UNPIVOT function
UNPIVOT is a function provided by Oracle that is specifically used for column conversion. It can convert columns in a table into rows and specify which columns need to be converted. The basic syntax is as follows:
SELECT job,deptno,salary
FROM scott.t_pivoted_emp
UNPIVOT
(salary
FOR deptno
IN (DEPTNO_10_SAL_SUM AS 10,DEPTNO_20_SAL_SUM AS 20,DEPTNO_30_SAL_SUM AS 30))
ORDER BY job, deptno;
JOB DEPTNO SALARY
--------- ---------- ----------
ANALYST 20 6000
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
PRESIDENT 10 5000
SALESMAN 30 5600
In this example,DEPTNO_10_SAL_SUM，DEPTNO_20_SAL_SUMand DEPTNO_30_SAL_SUMwill be converted into rows, andJOBand converted values (byvalueColumns represent) will remain as columns.
3. Custom functions and procedures
For very specific needs, you may need to write custom PL/SQL functions or procedures to implement column-to-line conversion. This approach provides maximum flexibility, but also requires corresponding programming skills and a deep understanding of Oracle PL/SQL.
Summarize
In practical applications, the most suitable column-to-line conversion method should be selected according to specific needs and data structures. For most common scenarios, UNPIVOT function and CASE statement combined with UNION ALL are two simple and effective solutions. For more complex scenarios, you may need to consider usingCONNECT BY LEVELOr write custom functions.