透视表在琴弦下透视列分组？(Pivot table strings grouping under p

JOB        ENAME
--------  ----------
ANALYST    SCOTT
ANALYST    FORD
CLERK      SMITH
CLERK      ADAMS
CLERK      MILLER
CLERK      JAMES
MANAGER    JONES
MANAGER    CLARK
MANAGER    BLAKE
PRESIDENT  KING
SALESMAN   ALLEN
SALESMAN   MARTIN
SALESMAN   TURNER
SALESMAN   WARD

我想格式化结果设定，使得每个职业都有自己的列：

CLERKS  ANALYSTS  MGRS   PREZ  SALES
------  --------  -----  ----  ------
MILLER  FORD      CLARK  KING  TURNER
JAMES   SCOTT     BLAKE        MARTIN
ADAMS             JONES        WARD
SMITH 

我试过了

SELECT ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN from
(
  SELECT ename, job from emp
) as st
pivot
(
  SELECT ename
  FOR job in (ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN)
) as pivottable

我得到这些错误

消息156，15级，状态1，第7行
关键字“选择”附近的语法不正确。
消息156，15级，状态1，8号线
“在”关键字附近的语法不正确。

如何使用枢轴组串下透视列？

Answer 1:

当您使用的PIVOT功能，你需要使用一个聚合函数。 一个语法PIVOT是：

从MSDN ：

SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    [last pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

一个字符串，则需要请使用MIN()或MAX()聚合函数。 你会遇到的问题是，这些功能将为每列仅返回一个值。

因此，为了得到PIVOT工作，你需要提供一个独特的价值，将让行的过程中分离GROUP BY 。

对于你的榜样，您可以使用row_number()

SELECT ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN 
from
(
  SELECT ename, job,
    row_number() over(partition by job order by ename) rn
  from emp
) as st
pivot
(
  max(ename)
  FOR job in (ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN)
) as pivottable

请参阅SQL拨弄演示 。

该row_number()创建了分配给每个行中的一个独特价值job ，当你应用聚合函数和GROUP BY在PIVOT你仍然会得到不同的行。

文章来源: Pivot table strings grouping under pivot column?