将数据透视表字符串分组在数据透视表列下? [英] Pivot table strings grouping under pivot column?

问题描述

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 

我尝试过

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行
关键字"SELECT"附近的语法不正确.
消息156，第15级，状态1，第8行
关键字'in'附近的语法不正确.

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

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

How to use pivot to group strings under pivot column?

推荐答案

使用来自 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()聚合函数.您将遇到的问题是这些函数将为每一列仅返回一个值.

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.

因此，为了使PIVOT正常工作，您需要提供一个不同的值，该值将在GROUP BY期间使各行分开.

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.

例如，您可以使用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中的每一行，当您应用聚合函数和PIVOT中的GROUP BY时，您仍将获得单独的行.

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.

这篇关于将数据透视表字符串分组在数据透视表列下?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！