事件表和日期范围之间的t-sql查询

Based on the following table

基于下表

id  Title   Date            Metadata  
------------------------------------
1   A       08/01/2010      M1
1   A       10/05/2010      M2
1   A       03/15/2011      M3
2   B       09/20/2010      M1
2   B       01/15/2011      M2
3   C       12/15/2010      M1

Input variables will be start and end date. So for instance,

输入变量将是开始和结束日期。例如,

@startDate = '07/01/2010' 
@endDate = '06/30/2011'

How to generate the below output?

如何生成下面的输出?

Title  Jul-10  Aug-10 Sep-10 Oct-10 Nov-10  Dec-10 Jan-11 Feb-11 Mar-11 Apr-11 May-11 Jun-11
-------------------------------------------------------------------------------------------
A      Null    M1     Null    M2     Null   Null   Null    Null   M3     Null   Null   Null
B      Null    M1     Null    Null   Null   Null   M2      Null   Null   Null   Null   Null
C      Null    Null   Null    Null   Null   M1     Null    Null   Null   Null   Null   Null

2 个解决方案

#1

What you are seeking is commonly called a crosstab query. If what you are asking is how to build a crosstab query given a static list of columns, you can do something like so:

您正在寻找的通常称为交叉表查询。如果您要问的是如何在给定静态列列表的情况下构建交叉表查询，您可以这样做:

Select Title
    , Min( Case When DatePart(mm, [Date]) = 7 And DatePart(yy, [Date]) = 2010 Then MetaData End ) As [Jul-10]
    , Min( Case When DatePart(mm, [Date]) = 8 And DatePart(yy, [Date]) = 2010 Then MetaData End ) As [Aug-10]   
    , Min( Case When DatePart(mm, [Date]) = 9 And DatePart(yy, [Date]) = 2010 Then MetaData End ) As [Sep-10]       
    ...
From Table
Where [Date] Between @StartDate And @EndDate
Group By Title

Similarly, you can use the PIVOT functionality as suggested by Broken Link. However, both the above solution and the PIVOT functionality rely on static column declarations. If what you want is a dynamic list of columns (a.k.a. dynamic crosstab), then you are outside the bounds of what T-SQL was primarily designed to do. It is possible with some fugly dynamic SQL but it is brittle and cumbersome. Instead, you should build the resultset in a middle-tier component or use a reporting tool that will build crosstab results.

类似地，可以使用断开链接建议的PIVOT功能。但是，上面的解决方案和PIVOT功能都依赖于静态列声明。如果您想要的是一个动态列列表(即动态交叉表)，那么您就超出了T-SQL最初设计的范围。使用一些笨拙的动态SQL是可能的，但它很脆弱，很麻烦。相反，您应该在中间层组件中构建resultset，或者使用一个报告工具来构建交叉表结果。

#2

Use Pivot tables..

使用数据透视表. .

A simple example..

一个简单的例子。

    USE AdventureWorks;
GO

SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture; 



DaysToManufacture  AverageCost  
0                  5.0885  
1                  223.88  
2                  359.1082  
4                  949.4105

Query

查询

    SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,   
[0], [1], [2], [3], [4]  
FROM  
(SELECT DaysToManufacture, StandardCost   
    FROM Production.Product) AS SourceTable  
PIVOT  
(  
AVG(StandardCost)  
FOR DaysToManufacture IN ([0], [1], [2], [3], [4]) 
) AS PivotTable;

Result

结果

Cost_Sorted_By_Production_Days   0                     1                     2                     3                     4

AverageCost                    5.0885                223.88                359.1082              NULL                  949.4105

#1