Cross Tab - 在同一列中存储不同的日期(Meeting1、Meeting2、Meeting 3 等)

2022-01-22 00:00:00 pivot sql tsql sql-server

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

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).

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

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:

日期类型表

 1           | DDR1
 2           | DDR2
 3           | DueDate


任务表

1 | Create Design
2 | Submit Paperwork


Tasks_DateType 表

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


结果应该是:

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

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

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

编辑

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

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

相关文章