代码之家 › 专栏 › 技术社区 › Nathan W

使用布尔字段作为行标题的跨选项卡查询

crosstab ms-access sql-server

0

Nathan W · 技术社区 · 16 年前

我目前有一个表结构,看起来像这样(省略了一些细节):

  ColumnName         || Type
  Date_Of_Job           DateTime
  Reparied_Service      Boolean 
  Disconnect_Service    Boolean 
  Relayed_Service       Boolean 
  Reparied_Stopcock     Boolean 
  Replaced_Stopcock     Boolean 
  TPFNR_Repaired        Boolean 
  TPFNR_Replaced        Boolean 
  TPFNR_Capped          Boolean 
  Poor_Pressure_Tested  Boolean 
  Flow_Test             Boolean 
  Meter_replaced        Boolean

工作日期

==Job Type===========01=====02=====03 etc
Reparied_Service     5      20     30
Disconnect_Service   15     45      9
Relayed_Service      18      7      6
Reparied_Stopcock    18 
Replaced_Stopcock    20 
 etc

已修复_旋塞阀 Flow_Test 对于一个记录。

有人知道我如何在access或MS SQL中做到这一点吗,哪一个并不重要。

1 回复 | 直到 16 年前

1

HansUp 16 年前

假设Date_Of_Job值来自同一年,则可以将Access交叉选项卡查询基于UNION子选择。

TRANSFORM Count(q.[job_type]) AS num_jobs
SELECT q.[job_type]
FROM (
    SELECT
        Month(Date_Of_Job) AS job_month
        , "Reparied_Service" AS job_type
    FROM Jobs
    WHERE Reparied_Service=True
    UNION ALL
    SELECT
        Month(Date_Of_Job) AS job_month
        , "Disconnect_Service" AS job_type
    FROM Jobs
    WHERE Disconnect_Service=True
    ) AS q
GROUP BY q.[job_type]
PIVOT q.[job_month];