帮助!无法获取所需的结果

Question

使用SQL Server2008(R2)

我有一个结果集，我需要以不同的方式显示，它目前可用如下所示：

Resource    Nr  Cost    StartDate   EndDate  
Cable_5m    8   3.5 12/03/2011  13/03/2011  
Cable_5m    2   3.5 13/03/2011  14/03/2011  
Rope125A    1   0   16/03/2011  18/03/2011  
Rope125A    1   0   17/03/2011  17/03/2011  

我需要的是在日期的基础上报告每个资源的发送数量，如下所示：

DateOfEvnt Resource Nr Cost  
2011-03-12 Cable_5m 8 3.50  
2011-03-13 Cable_5m 10 3.50   
2011-03-14 Cable_5m 2 3.50  
2011-03-16 Rope125A 1 0.00  
2011-03-17 Rope125A 2 0.00  
2011-03-18 Rope125A 1 0.00

我看过pivot，但终生无法让它工作，我不确定pivot是我所需要的。所有的帮助都会被感激地接受。

Answer 1

对于给定的输入和输出，根本不需要PIVOT。一个简单的UNION和GROUP BY就足够了。

测试数据

DECLARE @Resources TABLE (
    Resource VARCHAR(32)
    , Nr INTEGER
    , Cost FLOAT
    , StartDate DATE
    , EndDate DATE
)

INSERT INTO @Resources 
    SELECT 'Cable_5m', 8, 3.5, '03/12/2011', '03/13/2011'
    UNION ALL SELECT 'Cable_5m', 2, 3.5, '03/13/2011', '03/14/2011'
    UNION ALL SELECT 'Rope125A', 1, 0,   '03/16/2011', '03/18/2011' 
    UNION ALL SELECT 'Rope125A', 1, 0,   '03/17/2011', '03/17/2011'

SQL语句

SELECT  DateOfEvnt
        , Resource
        , Nr = SUM(Nr)
        , Cost      
FROM    (           
            SELECT  Resource
                    , Nr
                    , Cost
                    , [DateOfEvnt] = StartDate
            FROM    @Resources
            UNION ALL       
            SELECT  Resource
                    , Nr
                    , Cost
                    , [DateOfEvnt] = EndDate
            FROM    @Resources
        ) r         
GROUP BY
        Resource
        , Cost
        , DateOfEvnt

编辑

虽然UNION和GROUP BY满足了结果，但我开始怀疑您更需要这样的东西，即WITH语句返回Start-和EndDate之间每天的记录。

;WITH q AS (
    SELECT  Resource
            , Nr
            , Cost
            , StartDate
            , DateOfEvnt = StartDate            
    FROM    @Resources
    UNION ALL
    SELECT  q.Resource
            , q.Nr
            , q.Cost
            , q.StartDate
            , DATEADD(day, 1, q.DateOfEvnt)         
    FROM    q
            INNER JOIN @Resources s ON s.Resource = q.Resource
                                       AND s.Nr = q.Nr
                                       AND s.StartDate = q.StartDate
    WHERE   q.DateOfEvnt < s.EndDate
)
SELECT  DateOfEvnt
        , Resource
        , Nr = SUM(Nr)
        , Cost
FROM    q
GROUP BY
        DateOfEvnt
        , Resource
        , Cost