SQL 带 MONTH() 的 PIVOT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7145694/
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 with MONTH()
提问by Ueli
How can I pivot for a column with dates as month?
如何为日期为月份的列进行透视？
Example:
例子：
Data:
数据：
|-----------------------------------|
| def_kstnr | def_zeit | def_datum |
|-----------------------------------|
| 100 | 3.2 | 2011-11-02 |
| 110 | 2.8 | 2011-02-03 |
| 120 | 5.4 | 2011-11-04 |
| 130 | 2.4 | 2011-08-05 |
| 140 | 4.9 | 2011-09-06 |
| 150 | 1.5 | 2011-10-07 |
| 160 | 2.6 | 2011-12-08 |
|-----------------------------------|
Query:
询问：
SELECT
def_kstnr,
[1] AS Jan,
[2] AS Feb,
[3] AS Mrz,
[4] AS Apr,
[5] AS Mai,
[6] AS Jun,
[7] AS Jul,
[8] AS Aug,
[9] AS Sep,
[10] AS Okt,
[11] AS Nov,
[12] AS Dez
FROM
dbo.def
PIVOT
(
SUM(def_zeit)
FOR MONTH(def_datum)
IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
) AS pvtMonth
GROUP BY
dbo.def.def_kstnr,
MONTH(def_datum)
I get this error:
我收到此错误：
Incorrect syntax near '('.
'(' 附近的语法不正确。
(Line with "FOR MONTH(def_datum)")
（带有“FOR MONTH（def_datum）”的行）
Result should look like this:
结果应如下所示：
|-----------------------------------------------------------------------------------|
| def_kstnr | Jan | Feb | Mrz | Apr | Mai | Jun | Jul | Aug | Sep | Okt | Nov | Dez |
|-----------------------------------------------------------------------------------|
| 100 | | | | | | | | | | | 3.2 | |
| 110 | | 2.8 | | | | | | | | | | |
...
|-----------------------------------------------------------------------------------|
Thank you :)
谢谢 ：）
回答by Xhalent
If you move the Month function into a prior sourcetable the PIVOT works. Note I don't think you need to go grouping things following the pivot.
如果您将 Month 函数移动到先前的源表中，PIVOT 将起作用。请注意，我认为您不需要按照支点对事物进行分组。
SELECT
def_kstnr,
[1] AS Jan,
[2] AS Feb,
[3] AS Mrz,
[4] AS Apr,
[5] AS Mai,
[6] AS Jun,
[7] AS Jul,
[8] AS Aug,
[9] AS Sep,
[10] AS Okt,
[11] AS Nov,
[12] AS Dez
FROM
(Select
def_kstnr,
def_zeit,
MONTH(def_datum) as TMonth
from
dbo.def) source
PIVOT
(
SUM(def_zeit)
FOR TMonth
IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
) AS pvtMonth
回答by Shashikant Pandit
--Create Temporary Sales Table
CREATE TABLE #Sales
(SalesId INT IDENTITY(1,1), SalesDate DateTime)
GO
--Populate 1000 Sample Sales Records With
--Random past 0-798 days as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(dd, - CONVERT(INT, (798+1)*RAND()),GETDATE()))
GO 1000
Demo 1: Getting Monthly Data
演示 1：获取月度数据
SELECT YEAR(SalesDate) [Year], MONTH(SalesDate) [Month],
DATENAME(MONTH,SalesDate) [Month Name], COUNT(1) [Sales Count]
FROM #Sales
GROUP BY YEAR(SalesDate), MONTH(SalesDate),
DATENAME(MONTH, SalesDate)
ORDER BY 1,2
RESULT:-
结果：-
Demo 2: Getting Monthly Data using PIVOT
演示 2：使用 PIVOT 获取月度数据
SELECT *
FROM (SELECT YEAR(SalesDate) [Year],
DATENAME(MONTH, SalesDate) [Month],
COUNT(1) [Sales Count]
FROM #Sales
GROUP BY YEAR(SalesDate),
DATENAME(MONTH, SalesDate)) AS MontlySalesData
PIVOT( SUM([Sales Count])
FOR Month IN ([January],[February],[March],[April],[May],
[June],[July],[August],[September],[October],[November],
[December])) AS MNamePivot
RESULT:-
结果：-
