使用布尔字段作为行标题的交叉表查询

发布于 2024-08-04 05:23:56 字数 980 浏览 3 评论 0原文

我目前有一个看起来像这样的表结构（省略了一些细节）：

  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

我希望能够显示的是一个交叉表查询，其中月份（Date_Of_Job）作为列标题和不同的作业类型作为行标题以及该月每个作业的计数。

像这样的事情：

==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

我遇到的问题是每个作业类型都存储为布尔列，因为每个记录可以包含多个作业类型。例如，您可以为一条记录记录 Reparied_Stopcock 作业以及 Flow_Test。

有谁知道我如何在 Access 或 MS SQL 中执行此操作，哪一个并不重要。

原文

I currently have a table structure that looks something like this(some details omitted):

  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

What I want to be able to show is a cross tab query with the month(Date_Of_Job) as the column headers and the different types of jobs as the row headers with the count of each job for that month.

So something like this:

==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

The problem that I have is that the each job type is stored as a boolean column because each record can included multiple job type. For example you can log a Reparied_Stopcock job as well as a Flow_Test for the one record.

Does anyone know how I can do this in access or MS SQL, it doesn't really matter which one.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

独守阴晴ぅ圆缺 2024-08-11 05:23:56

假设 Date_Of_Job 值来自同一年，您可以基于 UNION 子选择进行 Access 交叉表查询。

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];

Assuming Date_Of_Job values are from the same year, you can base an Access cross tab query on a UNION subselect.

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];

回复收藏 0 原文

~没有更多了~