How row to column is implemented in Oracle

In the development of the report, it is unavoidable to encounter the problem of row to column.

For example, in the case of Scott's EMP in Oracle, you can use "Row to column" to count the number of people in each position in each department:

The original data for Scott's EMP are:

EMPNO	Ename	JOB	MGR	HireDate	SAL	COMM	DEPTNO
7369	SMITH	Clerk	7902	12/17/1980	800.00		20
7499	ALLEN	Salesman	7698	2/20/1981	1600.00	300.00	30
7521	WARD	Salesman	7698	2/22/1981	1250.00	500.00	30
7566	JONES	MANAGER	7839	4/2/1981	2975.00		20
7654	MARTIN	Salesman	7698	9/28/1981	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	5/1/1981	2850.00		30
7782	CLARK	MANAGER	7839	6/9/1981	2450.00		10
7788	SCOTT	ANALYST	7566	4/19/1987	3000.00		20
7839	KING	President		11/17/1981	5000.00		10
7844	TURNER	Salesman	7698	9/8/1981	1500.00	0.00	30
7876	Adams	Clerk	7788	5/23/1987	1100.00		20
7900	JAMES	Clerk	7698	12/3/1981	950.00		30
7902	FORD	ANALYST	7566	12/3/1981	3000.00		20
7934	MILLER	Clerk	7782	1/23/1982	1300.00		10

Use row to column to count the number of people in each position in each department after the data is:

JOB	Ten (DEPTNO)	(DEPTNO)	(DEPTNO)	(DEPTNO)
Clerk	1	2	1	0
Salesman	0	0	4	0
President	1	0	0	0
MANAGER	1	1	1	0
ANALYST	0	2	0	0

First, the classic way of realization

The main use of Decode functions, aggregate functions (such as Max, SUM, etc.), the Group by group implementation of the

SelectT.job,Count(Decode (T.deptno,'Ten',1)) as"Ten(DEPTNO) ",Count(Decode (T.deptno,' -',1)) as" -(DEPTNO) ",Count(Decode (T.deptno,' -',1)) as" -(DEPTNO) ",Count(Decode (T.deptno,' +',1)) as" +(DEPTNO) " fromscott.emp TGroup  byT.job;

Classical Approach

Second, PIVOT

After Oracle 11g, pivot appears to make it easier to implement row-to-column. before you use, determine that the database environment is greater than 11g

 with  as (    Select  t.job, T.deptno      from  scott.emp t)Select *  from Tmp_tab t pivot (count(1 for in (20  ());

PIVOT

Third, PIVOT XML

Using the classic method and the pivot method, the parameters of the Deptno are hard-coded. and through pivot XML can solve this problem, so that the conditions can be dynamic. However, the output is the CLOB format of the XML. Currently,Java read pivot XML Clob seems difficult (I did not read successfully, see the following description, if there is known, please know).

 with  as (    Select  t.job, T.deptno      from  scott.emp t)Select *  from Tmp_tab t pivot XML (count(1 for in (select  from scott.dept));

PIVOT XML

However, when writing the above pivot XML drop, using Java to read the data, but found that the clob of the pivot XML is not read (normal and the same data clob can read normally)

Efforts for several days, but also try to download the latest OJDBC, but still error.

Error is

• "Invalid column Type:getclob not implemented for class Oracle.jdbc.driver.T4CNamedTypeAccessor"--ojdbc6.jar