PIVOT

　　

select LC, count(
CASE
  WHEN SUBSTR(STATE_CODE,1,1)='F'
  THEN 1
END) AS F_STATE, COUNT(
CASE
  WHEN SUBSTR(STATE_CODE,1,1)='M'
  THEN 1
END) AS M_STATE
from ACC group by LC order by LC;

PIVOT :

select * from 
(
select LC,substr(state_code,1,1) as state from ACC
)
PIVOT (count(*) for state in ('F' AS F_STATE, 'M' AS M_STATE, 'S' AS S_STATE)) 
ORDER BY LC ;

语法如下：

SELECT ...

FROM  ...

PIVOT [XML]

   (pivot_clause

    pivot_for_clause

    pivot_in_clause )

WHERE ...

（1）pivot_clause: definesthe columns to be aggregated (pivot is an aggregate operation);

--这个是指定  聚合函数 

（2）pivot_for_clause: definesthe columns to be grouped and pivoted;

--指定我们需要将行转成列的字段

（3）pivot_in_clause: definesthe filter for the column(s) in the pivot_for_clause (i.e. the range of valuesto limit the results to). The aggregations for each value in thepivot_in_clause will be transposed into a separate column (where appropriate).

--对pivot_for_clause 指定的列进行过滤，只将指定的行转成列。

如：

SQL> WITH pivot_data AS (

 2          SELECT deptno, job, sal

 3          FROM   emp

 4          )

 5  SELECT *

 6  FROM   pivot_data

 7  PIVOT (

 8         SUM(sal)        --<-- pivot_clause

 9         FOR deptno          --<-- pivot_for_clause

 10        IN  (10,20,30,40)   --<-- pivot_in_clause

 11       );
---------------------  

具体语法：

SELECT ...

FROM  ...

UNPIVOT [INCLUDE|EXCLUDE NULLS]

   (unpivot_clause

    unpivot_for_clause

    unpivot_in_clause )

WHERE ...

（1）The INCLUDE | EXCLUDE NULLS clausegives you the option of including or excluding null-valued rows. INCLUDE NULLS causesthe unpivot operation to include null-valued rows; EXCLUDE NULLS eliminatesnull-values rows from the return set. If you omit this clause, then the unpivotoperation excludes nulls.

--这个选项用来控制unpivot 是否包含null 的记录，默认是不包含nulls的。

（2）unpivot_clause: this clause specifies a name for a column to represent the unpivotedmeasure values.  

-- 对应的具体值

（3）Inthe pivot_for_clause, specify a name for eachoutput column that will hold descriptor values, such as quarter or product.

--对应转换后列的名称

（4）Inthe unpivot_in_clause, specify the input datacolumns whose names will become values in the output columns of the pivot_for_clause.These input data columns have names specifying a category value, such as Q1,Q2, Q3, Q4. The optional AS clause lets you map the input data columnnames to the specified literal values in the output columns.

            --具体列到行的列名

如：

SQL> SELECT *

  2  FROM  pivoted_data

  3  UNPIVOT (

  4       deptsal                   --<-- unpivot_clause

  5       FORsaldesc              --<-- unpivot_for_clause

  6       IN (d10_sal, d20_sal, d30_sal, d40_sal)  --<-- unpivot_in_clause

  7          );
---------------------

--unpivot 是将列转换成行，所以所有列的类型必须一致。