以版权年份范围为列标题的交叉表查询 [英] Crosstab query with copyright year range as column heading

问题描述

我要创建交叉表查询的数据如下:

I have the following data that I want to create a crosstab query:

 ID   CallNo     CopyrightYear
 1    AH         2000
 2    AB         2000
 3    BC         2000
 4    AH         2002
 5    ZA         2005
 6    BC         2001
 7    AP         2003

这是我目前拥有的交叉表查询:

This is the crosstab query that I currently have:

 TRANSFORM Count(Table1.[ID]) AS CountOfID
 SELECT Table1.[CallNo], Count(Table1.[ID]) AS [Total Of ID]
 FROM Table1
 GROUP BY Table1.[CallNo]
 PIVOT Table1.[CopyrightYear];

所以我的问题是如何对版权年份(例如2000年至2002年以及2003年至2005年等等)进行分组...

So my question is on how can I group copyright year like from 2000 to 2002 and 2003 to 2005 and so on...

预期结果:

CallNo      2000 to 2002    2003 to 2005
AB              1   
AH              2   
AP                              1
BC              2   
ZA                              1

推荐答案

这是您的答案:

TRANSFORM Count(tab1.[ID]) AS CountOfID
SELECT tab1.[CallNo], Count(tab1.[ID]) AS [Total Of ID]
FROM tab1
GROUP BY tab1.[CallNo]
PIVOT CStr(Int(([CopyrightYear]+1)/3)*3-1)+' to '+CStr(Int(([CopyrightYear]+1)/3)*3+1);

实际上，您可以仅在Int(([CopyrightYear]+1)/3)*3-1上旋转. CStr转换主要是为了装饰目的

In fact, you can just pivot on Int(([CopyrightYear]+1)/3)*3-1. The CStr conversion is mainly done for the cosmetic purposes

对于2000年至2004年，2005年至2009年等，表达式将是(请注意，这是4年范围内的值5):

For the years 2000 to 2004, 2005 to 2009, etc the expression will be (note that it is value 5 for the 4 year range):

TRANSFORM Count(tab1.[ID]) AS CountOfID
SELECT tab1.[CallNo], Count(tab1.[ID]) AS [Total Of ID]
FROM tab1
GROUP BY tab1.[CallNo]
PIVOT CStr(Int(([CopyrightYear])/5)*5)+' to '+CStr(Int(([CopyrightYear])/5)*5+4)

这篇关于以版权年份范围为列标题的交叉表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！