Cross Tab - Storing different dates (Meeting1, Mee

问题:

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.

回答1:

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

回答2:

If the pivoted columns are unknown (dynamic), then you'll have to build up your query manually in either ms-sql 2000 or 2005, ie with out without PIVOT.

This involves either executing dynamic sql in a stored procedure (generally a no-no) or querying a view with dynamic sql. The latter is the approach I generally go with.

For pivoting, I prefer the Rozenshtein method over case statements, as explained here:

http://www.stephenforte.net/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8

EDIT

You can also do this in linq-to-sql, but it emits some pretty inefficient code (at least when I view it through linqpad), so I don't recommend it. If you're still curious I can post an example of how to do it.

回答3:

I don't have personal experience with the pivot operator, it may provide a better solution.

But I've used a case statement in the past

SELECT 
    TaskDescription, 
    CASE(DateTypeID = 1, Tasks_DateType.Date) AS DDr1, 
    CASE(DateTypeID = 2, Tasks_DateType.Date) AS DDr2,
    ...
FROM Tasks 
    INNER JOIN Tasks_DateType  ON Tasks.ID = Tasks_DateType.TasksID
    INNER JOIN DateType ON Tasks_DateType.DateTypeID = DateType.DateTypeID
GROUP BY TaskDescription

This will work, but will require you to change the SQL whenever there are more Task descriptions added, so it's not ideal.

EDIT:

It appears as though the PIVOT keyword was added in SqlServer 2005, this example shows how to do a pivot query in both 2000 & 2005, but it is similar to my answer.

回答4:

Version-1: +simple, -must be changed every time DateType is added. So is not great for a dynamic solution:

SELECT      tt.ID,
            tt.TaskDescription,
            td1.Date AS DDR1,
            td2.Date AS DDR2,
            td3.Date AS DueDate
FROM        Tasks tt
LEFT JOIN   Tasks_DateType td1
        ON  td1.TasksID = tt.ID AND td1.DateTypeID = 1
LEFT JOIN   Tasks_DateType td2
        ON  td2.TasksID = tt.ID AND td2.DateTypeID = 2
LEFT JOIN   Tasks_DateType td3
        ON  td3.TasksID = tt.ID AND td3.DateTypeID = 3

Version-2: completely dynamic (with some limitations, but they can be handled - just google for it):

Dynamic pivot query creation. See Dynamic Cross-Tabs/Pivot Tables: you need to create one SP of UDF and then can use it for multiple purposes. This is the original post, to which you may find many links and improvements.

Version-3: just leave it for your client code to handle. I would not design my SQL to return a dynamic set of data, but rather handle it on the client (presentation layer). I just would not like to handle some dynamic columns that come as a result of my query, where I need to guess what is that exactly. The only reason I use Version-2 is when the result is presented directly as a table for a report. In all other cases for truly dynamic data I use client code. For example: having structure you have, how will you attach logic that field DueDate is mandatory - you cannot use DB constraints; how will you ensure that DDR1 is not higher then DDR2? If these are not separate (static) columns in the database (where you can use CONSTRAINTS), then the client code is the one that validates your data consistency.

Good luck!