sql - 数据透视表字符串在数据透视列下分组？

Question

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

我收到这些错误

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'.

如何使用数据透视表对数据透视表列下的字符串进行分组？

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 Fiddle with Demo .

当您应用聚合函数和 GROUP BY 时，row_number() 会创建一个分配给 job 中的每一行的不同值code> 在 PIVOT 中，您仍然会得到单独的行。