CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
stackoverflowuser
stackoverflowuser

Reputation: 22380

t-sql query between a table of events and a date range

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

Upvotes: 3

Views: 205

Answers (2)

Thomas
Thomas

Reputation: 64635

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.

Upvotes: 3

Broken Link
Broken Link

Reputation: 2440

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

Upvotes: 2

Related Questions