交叉表-在同一列中存储不同的日期(Meeting1，Meeting2，Meeting 3等) [英] Cross Tab - Storing different dates (Meeting1, Meeting2, Meeting 3 etc) in the same column

问题描述

我需要跟踪不同的日期(动态).因此，对于特定任务，您可能需要跟踪X个日期(例如DDR1会议日期，DDR2会议日期，到期日期等).

我的策略是创建一个表(DateTypeID，DateDescription)来存储每个日期的描述.然后，我可以创建主表(ID，TaskDescription，DateTypeID).因此，所有日期都将在一栏中，您可以通过查看TypeID来知道该日期代表什么.问题是将其显示在网格中.我知道我应该使用交叉表查询，但是我无法使其正常工作.例如，我在SQL Server 2000中使用Case语句来旋转表，以便每个列名称都是日期类型的名称.如果我们有以下表格:

DateType表

DateTypeID |日期说明

 1           | DDR1
 2           | DDR2
 3           | DueDate

任务表

ID | TaskDescription

1 | Create Design
2 | Submit Paperwork

Tasks_DateType表

TasksID | DateTypeID |日期

1       |     1         | 09/09/2009
1       |     2         | 10/10/2009
2       |     1         | 11/11/2009
2       |     3         | 12/12/2009

结果应该是:

TaskDescription | DDr1 | DDR2 | DueDate

Create Design     |09/09/2009 | 10/10/2009 | null
Submit Paperwork  |11/11/2009 | null       | 12/12/2009

如果有人知道我如何进行研究，我将不胜感激.我这样做而不是为每个日期创建一列的原因与该功能有关，该功能使用户将来可以根据需要添加任意数量的日期，而不必手动向表中添加列和编辑html代码.这也允许使用简单的代码比较日期或按类型显示即将执行的任务(例如，创建设计的DDR1日期即将到来").如果有人可以指出正确的方向，我将不胜感激.

解决方案

以下是根据您的数据进行测试的正确答案.我只使用了前两种日期类型，但是无论如何，您都可以随时进行构建.

Select 
    Tasks.TaskDescription,     
    Min(Case DateType.DateDescription When 'DDR1' Then Tasks_DateType.Date End) As DDR1,     
    Min(Case DateType.DateDescription When 'DDR2' Then Tasks_DateType.Date End) As DDR2
From
    Tasks_DateType
    INNER JOIN Tasks ON Tasks_DateType.TaskID = Tasks.TaskID
    INNER JOIN DateType ON Tasks_DateType.DateTypeID = DateType.DateTypeID
Group By
    Tasks.TaskDescription

编辑

范提到，没有日期的任务将不会显示.这是对的.使用左联接(再次由van提到)并稍微重构查询将返回所有任务，即使目前您不需要.

Select 
    Tasks.TaskDescription,     
    Min(Case DateType.DateDescription When 'DDR1' Then Tasks_DateType.Date End) As DDR1,     
    Min(Case DateType.DateDescription When 'DDR2' Then Tasks_DateType.Date End) As DDR2
From
    Tasks   
    LEFT OUTER JOIN Tasks_DateType ON Tasks_DateType.TaskID = Tasks.TaskID
    LEFT OUTER  JOIN DateType ON Tasks_DateType.DateTypeID = DateType.DateTypeID
Group By
    Tasks.TaskDescription

I need to keep track of different dates (dynamic). So for a specific Task you could have X number of dates to track (for example DDR1 meeting date, DDR2 meeting date, Due Date, etc).

My strategy was to create one table (DateTypeID, DateDescription) which would store the description of each date. Then I could create the main table (ID, TaskDescription, DateTypeID). So all the dates would be in one column and you could tell what that date represents by looking at the TypeID. The problem is displaying it in a grid. I know I should use a cross tab query, but i cannot get it to work. For example, I use a Case statement in SQL Server 2000 to pivot the table over so that each column name is the name of the date type. IF we have the following tables:

DateType Table

DateTypeID | DateDescription

 1           | DDR1
 2           | DDR2
 3           | DueDate

Tasks Table

ID | TaskDescription

1 | Create Design
2 | Submit Paperwork

Tasks_DateType Table

TasksID | DateTypeID | Date

1       |     1         | 09/09/2009
1       |     2         | 10/10/2009
2       |     1         | 11/11/2009
2       |     3         | 12/12/2009

THE RESULT SHOULD BE:

TaskDescription | DDr1 | DDR2 | DueDate

Create Design     |09/09/2009 | 10/10/2009 | null
Submit Paperwork  |11/11/2009 | null       | 12/12/2009

IF anyone has any idea how I can go about researching this, I appreciate it. The reason I do this instead of making a column for each date, has to do with the ability to let the user in the future add as many dates as they want without having to manually add columns to the table and editing html code. This also allows simple code for comparing dates or show upcoming tasks by their type (ex. 'Create design's DDR1 date is coming up' ) If anyone can point me in the right direction, I appreciate it.

解决方案

Here is a proper answer, tested with your data. I only used the first two date types, but you'd build this up on the fly anyway.

Select 
    Tasks.TaskDescription,     
    Min(Case DateType.DateDescription When 'DDR1' Then Tasks_DateType.Date End) As DDR1,     
    Min(Case DateType.DateDescription When 'DDR2' Then Tasks_DateType.Date End) As DDR2
From
    Tasks_DateType
    INNER JOIN Tasks ON Tasks_DateType.TaskID = Tasks.TaskID
    INNER JOIN DateType ON Tasks_DateType.DateTypeID = DateType.DateTypeID
Group By
    Tasks.TaskDescription

EDIT

van mentioned that tasks with no dates won't show up. This is correct. Using left joins (again, mentioned by van) and restructuring the query a bit will return all tasks, even though this is not your need at the moment.

Select 
    Tasks.TaskDescription,     
    Min(Case DateType.DateDescription When 'DDR1' Then Tasks_DateType.Date End) As DDR1,     
    Min(Case DateType.DateDescription When 'DDR2' Then Tasks_DateType.Date End) As DDR2
From
    Tasks   
    LEFT OUTER JOIN Tasks_DateType ON Tasks_DateType.TaskID = Tasks.TaskID
    LEFT OUTER  JOIN DateType ON Tasks_DateType.DateTypeID = DateType.DateTypeID
Group By
    Tasks.TaskDescription

这篇关于交叉表-在同一列中存储不同的日期(Meeting1，Meeting2，Meeting 3等)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！