SQL 数据透视表字符串在数据透视列下分组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15337214/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pivot table strings grouping under pivot column?
提问by mr_eclair
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'.
消息 156，级别 15，状态 1，第 7 行
关键字“SELECT”附近的语法不正确。
消息 156，级别 15，状态 1，第 8 行
关键字“in”附近的语法不正确。
How to use pivot to group strings under pivot column?
如何使用pivot对pivot列下的字符串进行分组？
回答by Taryn
When you are using the PIVOTfunction, you are required to use an aggregate function. The syntax of a PIVOTis:
使用PIVOT函数时，需要使用聚合函数。a 的语法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()orMAX()聚合函数。您将遇到的问题是这些函数将只为每一列返回一个值。
So in order to get the PIVOTto 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.
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 BYin the PIVOTyou will still get separate rows.
在row_number()创建分配给每个行中的一个独特的价值job，当你申请的聚合函数和，GROUP BY在PIVOT你仍然会得到不同的行。
