枢轴列下的数据透视表字符串分组？

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

I would like to format the result set such that each job gets its own column:

我想格式化结果集，以便每个作业都有自己的列：

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

I tried

我试过了

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

I'm getting these errors

我收到这些错误

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'in'.

Msg 156，Level 15，State 1，Line 7关键字'SELECT'附近的语法不正确。 Msg 156，Level 15，State 1，Line 8关键字'in'附近的语法不正确。

How to use pivot to group strings under pivot column?

如何使用数据透视表在枢轴列下分组字符串？

1 个解决方案

#1

When you are using the PIVOT function, you are required to use an aggregate function. The syntax of a PIVOT is:

使用PIVOT函数时，需要使用聚合函数。 PIVOT的语法是：

From MSDN:

来自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>;

With a string, you will need to use either the MIN() or MAX() aggregate function. The problem that you will run into is that these functions will return only one value for each column.

使用字符串，您将需要使用MIN（）或MAX（）聚合函数。您将遇到的问题是这些函数将仅为每列返回一个值。

So in order to get the PIVOT to work, you will need to provide a distinct value that will keep the rows separate during the GROUP BY.

因此，为了使PIVOT工作，您需要提供一个独特的值，以便在GROUP BY期间保持行分离。

For your example, you can use row_number():

对于您的示例，您可以使用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

See SQL Fiddle with Demo.

请参阅SQL Fiddle with Demo。

The row_number() creates a distinct value that is assigned to each row in the job, when you apply the aggregate function and the GROUP BY in the PIVOT you will still get separate rows.

row_number（）创建一个分配给作业中每一行的不同值，当您在PIVOT中应用聚合函数和GROUP BY时，您仍将获得单独的行。

#1