带有MONTH()的PIVOT [英] PIVOT with MONTH()
本文介绍了带有MONTH()的PIVOT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何旋转日期为月的列?
How can I pivot for a column with dates as month?
示例:
数据:
|-----------------------------------|
| 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 |
|-----------------------------------|
查询:
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)
我收到此错误:
'('附近的语法不正确.
Incorrect syntax near '('.
(以"FOR MONTH(def_datum)"开头)
(Line with "FOR MONTH(def_datum)")
结果应如下所示:
|-----------------------------------------------------------------------------------|
| def_kstnr | Jan | Feb | Mrz | Apr | Mai | Jun | Jul | Aug | Sep | Okt | Nov | Dez |
|-----------------------------------------------------------------------------------|
| 100 | | | | | | | | | | | 3.2 | |
| 110 | | 2.8 | | | | | | | | | | |
...
|-----------------------------------------------------------------------------------|
谢谢:)
推荐答案
如果将月"功能移到先前的源表中,则PIVOT可以使用.请注意,我认为您无需按照数据透视表进行分组.
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.
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
这篇关于带有MONTH()的PIVOT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文