oracle10g alternative to row-and-column conversion functions

Executed under the Oracle sample database Scott

Select Empno,ename,job,sal,deptno from emp order by Deptno,job;

--Row to column
--now check the total salary of each department in various jobs.

Select Deptno, Job, sum (SAL) total_sal from EMP Group by DEPTNO, job order by 1, 2;

--but this is not intuitive, if you can display each job as 1 columns will be more visible.
-This is the need for row-to-column.
--Before 11g, it takes a bit of skill to use the Decode function to accomplish this goal.

Select Deptno, sum (decode (Job, ' president ', Sal, 0)) as President_sal, sum (decode (Job, ' MANAGER ', sal, 0)) as Manager_sal, Sum (decode (Job, ' ANALYST ', sal, 0)) as Analyst_sal, sum (decode (Job, ' clerk ', Sal, 0)) as Clerk_sal, sum (decode (Job, ' SALE Sman ', sal, 0)) as Salesman_sal from EMP Group BY Deptno ORDER by 1;

Select Deptno, sum (case when job= ' president "then Sal else 0 end) as President_sal, sum (case when job= ' MANAGER ' then Sal E  LSE 0 end) as Manager_sal, sum (case when job= ' ANALYST ' and SAL else 0 end) as Analyst_sal, sum (case is job= ' clerk ' then Sal else 0 end) as Clerk_sal, sum (case if job= ' salesman ' then SAL else 0 end) as Salesman_sal from EMP Group by Deptno Order by 1;

--If you want to return to the previous results, you need to use the Cartesian product, a row of five lines, and then use decode. For example:

With T as (select Deptno, sum (decode (Job, ' president ', Sal, 0)) as President_sal, sum (decode (Job, ' MANAGER ', sal, 0)) as Manager_sal, sum (decode (Job, ' ANALYST ', sal, 0)) as Analyst_sal, sum (decode (Job, ' clerk ', Sal, 0)) as Clerk_sal, sum (decod E (Job, ' salesman ', Sal, 0)) as Salesman_sal from EMP Group by DEPTNO) Select Deptno, decode (LVL, 1, ' president ', 2, ' MANA GER ', 3, ' ANALYST ', 4, ' clerk ', 5, ' salesman ') as JOB, decode (LVL, 1, president_sal, 2, Manager_sal, 3, Analyst_sal, 4, CL Erk_sal, 5, salesman_sal) as Total_sal from T, (select level LVL from dual connect by Level <= 5) Order by 1, 2;

After--11g, Oracle adds pivot and UNPIVOT statements, which makes it easy to complete the conversion.
--pivot
--First look at Pivot's syntax is

SELECT .... From <table-expr> PIVOT (Aggregate-function (<column>) for <pivot-column> in (<value1>, < Value2>,..., <valuen>) as <alias> WHERE .....

　

SELECT * FROM (select Deptno, Job, sal from EMP) pivot (SUM (SAL) for job in (the value after---in is converted to column name actual conversion: sum (case job= ' Presi DENT ' then Sal else 0 end) as President_sal ' president ' as President_sal, ' MANAGER ' as Manager_sal, ' ANALYST ' as Analyst_sa L, ' clerk ' as Clerk_sal, ' salesman ' as Salesman_sal)) Order by 1;

--In fact, Oracle does an implicit group by for columns outside the columns that appear in the pivot clause.
--Now, if you want to add 1 columns to your results, you can do that by showing your department's total salary.

-2-point description,
--1) Oracle does an implicit group by for columns other than columns that appear in the pivot clause, that is, Deptno and sal_total.
--The Analytic function is used here, which is unique for each deptno,sal_total, so the result of group by IS 3 rows.

SELECT * FROM (select Deptno, Job, sal from EMP) pivot (SUM (SAL) as Sal_total, Count (Sal) as emp_total for job in (---Actual conversion : Sum (case if job= ' clerk ' then Sal else 0 end) as Clerk_sal_total--count (case is job= ' clerk ' then sale end) as Clerk_em P_total ' clerk ' as Clerk, ' salesman ' as Salesman ') Order by 1;

-2) Oracle will splice the column name = alias in the FOR clause + aggregate function alias, such as ' president ' + ' _ ' + ' sal_total '.

--You can specify multiple aggregate functions, such as statistical payroll sum and total number of people:

Select Deptno,sum (case if job= ' clerk ' then Sal else 0 end) as Clerk_sal_total,count (case when job= ' clerk ' then Sal end) As Clerk_emp_total,sum (case is job= ' salesman ' then Sal else 0 end) as Salesman_sal_total,count (case when job= ' salesman ' Then Sal end) as Salesman_emp_totalfrom Empgroup by Deptnoorder by 1;

The--FOR clause can specify multiple columns,
--To do this, first add 1 column rank to the EMP table, take the value of ' A ', ' B ',

--now, the number of people who want to count salesman and clerk, rank A and rank B, respectively.

--Career change
Syntax for--unpivot:

SELECT .... From <table-expr> UNPIVOT [include Nulls|exclude nulls] ((<column>) for <pivot-column> in (<value1& gt;, <value2>,..., <valuen>)) as <alias> WHERE .....

--Use the Unpivote statement to do column-to-row conversions

---If you add an include nulls clause

With T as (SELECT * FROM (select Deptno, Job, sal from EMP) pivot (SUM (SAL) to Job in (' Clerk ' as Clerk_sal, ' salesman '  As Salesman_sal)) SELECT * from T unpivot include nulls (Sal_total for JOB in (clerk_sal as ' clerk ', salesman_sal as ' salesman ')) Order by 1, 2;

--Multiple Pivot-column can be specified

---return XML format data

oracle10g alternative method for row-and-column conversion functions (RPM)