数据透视表错误 [英] Pivot table error

问题描述

为什么，为什么，为什么?我会收到错误消息吗?

WHY, why, why? Do I get an error:

"Msg 325，第15层，状态1，第17行 "PIVOT"附近的语法不正确.您可能需要将当前数据库的兼容性级别设置为更高的值才能启用此功能.请参见有关存储过程sp_dbcmptlevel的帮助." 对于这个查询?

"Msg 325, Level 15, State 1, Line 17 Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel." For this query?

WITH Offnet7 AS (
    SELECT  disposition.dispositiondesc, interaction.dispositionid, DATEPART(wk,interaction.ibegintime) as iWeek

    FROM    interaction INNER JOIN
            disposition ON interaction.reasonid = disposition.dispositionid

    WHERE   interaction.dispositionid = 10 and (reasonid = 20365 or reasonid = 20366 or reasonid = 11168) and
            interaction.ibegintime >= '2013-1-1' and
            interaction.ibegintime < '2014-1-1'
)

SELECT  iWeek, dispositiondesc, count(iWeek) as 'OffnetCounts'
FROM Offnet7 

Group by dispositiondesc, iWeek

PIVOT
(
    OffnetCounts
    for iWeek in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15])  
) AS counts

试图制作一个SQL提琴，当我进入"Build Schema"时，它感到窒息. (SQL Noob) 我从SQL Management Studio的对象资源管理器中提取了类型，并复制了一些示例数据.但这是我尝试过的:

Tried to make a SQL Fiddle, and it choked when I went to "Build Schema." (SQL Noob) I pulled the types from SQL management studio's object explorer and copied some example data. But this is what I attempted:

CREATE TABLE interaction
    ([reasonid] int, [dispositionid] int, [ibegintime] datetime)
;

INSERT INTO interaction
    ([reasonid], [dispositionid], [ibegintime])
VALUES
    (20366, 10, '2012-01-31 23:59:48.000'),
    (20366, 10, '2012-02-07 14:03:01.000'),
    (20366, 10, '2012-02-07 14:06:48.000'),
    (20366, 10, '2012-02-13 21:44:10.000'),
    (20366, 10, '2012-02-27 21:36:33.000')
;


CREATE TABLE disposition
    ([dispositionid] int, [predefined] int, [dispositiondesc] varchar(64), [displayvalue] varchar(254))
;

INSERT INTO disposition
    ([dispositionid], [predefined], [dispositiondesc], [displayvalue])
VALUES
(10, 1, 'TRANSFERRED OFFNET', 'TRANSFERRED OFFNET'),
    (11168, 0, 'TAKEDA PASSWORD', 'TAKEDA PASSWORD'),
    (15433, 0, 'Voice Mail - TAKEDAEMEA', 'Voice Mail - TAKEDAEMEA'),
    (20365, 0, 'TAKEDA iPAD, iPhone or BlackBerry', 'TAKEDA iPAD, iPhone or BlackBerry'),
    (20366, 0, 'TAKEDA Concur', 'TAKEDA Concur')
;

结论: 感谢Bluefeet的所有帮助！

Conclusion: Thanks for all the help Bluefeet!

对于对此感兴趣的任何人，如果我的DBA正确设置了SQL兼容性级别，那么他的第一个答案将起作用.在尝试了他的第一个答案之后，我得到了一个答案:

For anyone interested in this, his first answer WOULD HAVE worked if the SQL compatibility level was set correctly by my DBA. After trying his first answer I got a:

"Msg 102, Level 15, State 1, Line 19 Incorrect syntax near '('."

因为DBA没有为SQL Server配置支持PIVOT语句的兼容性级别.

Because the DBA doesn't have the SQL Server configured with a compatibility level that supports the PIVOT statement.

推荐答案

您的语法已关闭. PIVOT正在执行GROUP BY和聚合.在我看来，您想使用:

Your syntax is off. The PIVOT is doing the GROUP BY and an aggregation. It seems to me that you want to be using:

WITH Offnet7 AS 
(
  SELECT  disposition.dispositiondesc, 
    interaction.dispositionid, 
    DATEPART(wk,interaction.ibegintime) as iWeek
  FROM    interaction 
  INNER JOIN disposition 
    ON interaction.reasonid = disposition.dispositionid
  WHERE   interaction.dispositionid = 10 
    and (reasonid = 20365 or reasonid = 20366 or reasonid = 11168) 
    and interaction.ibegintime >= '2013-1-1' 
    and interaction.ibegintime < '2014-1-1'
)
SELECT  dispositiondesc, 
  [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15]
FROM Offnet7 
PIVOT
(
    count(dispositionid)
    for iWeek in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15])  
) AS counts;

请参见演示

这将创建一个数据表，其中包含每周dispositionid的计数，并按dispositiondesc分组.

This will create a table of data with the counts of the dispositionid's for each week, grouped by the dispositiondesc.

编辑，也可以使用带有CASE表达式的聚合函数来完成此操作:

Edit, this can also be done using an aggregate function with a CASE expression:

SELECT  disposition.dispositiondesc, 
  sum(case when DATEPART(wk,interaction.ibegintime) = 1 then 1 else 0 end) [1],
  sum(case when DATEPART(wk,interaction.ibegintime) = 2 then 1 else 0 end) [2],
  sum(case when DATEPART(wk,interaction.ibegintime) = 3 then 1 else 0 end) [3],
  sum(case when DATEPART(wk,interaction.ibegintime) = 4 then 1 else 0 end) [4],
  sum(case when DATEPART(wk,interaction.ibegintime) = 5 then 1 else 0 end) [5],
  sum(case when DATEPART(wk,interaction.ibegintime) = 6 then 1 else 0 end) [6],
  sum(case when DATEPART(wk,interaction.ibegintime) = 7 then 1 else 0 end) [7],
  sum(case when DATEPART(wk,interaction.ibegintime) = 8 then 1 else 0 end) [8],
  sum(case when DATEPART(wk,interaction.ibegintime) = 9 then 1 else 0 end) [9],
  sum(case when DATEPART(wk,interaction.ibegintime) = 10 then 1 else 0 end) [10],
  sum(case when DATEPART(wk,interaction.ibegintime) = 11 then 1 else 0 end) [11],
  sum(case when DATEPART(wk,interaction.ibegintime) = 12 then 1 else 0 end) [12],
  sum(case when DATEPART(wk,interaction.ibegintime) = 13 then 1 else 0 end) [13],
  sum(case when DATEPART(wk,interaction.ibegintime) = 14 then 1 else 0 end) [14],
  sum(case when DATEPART(wk,interaction.ibegintime) = 15 then 1 else 0 end) [15]
FROM    interaction 
INNER JOIN disposition 
  ON interaction.reasonid = disposition.dispositionid
WHERE   interaction.dispositionid = 10 
  and (reasonid = 20365 or reasonid = 20366 or reasonid = 11168) 
  and interaction.ibegintime >= '2013-1-1' 
  and interaction.ibegintime < '2014-1-1'
group by disposition.dispositiondesc;

请参见带有演示的SQL提琴

这篇关于数据透视表错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！