帮助！无法得到所需的结果

Question

使用 SQL Server 2008(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

我有看过枢轴，但我一生都无法让它发挥作用，而且我不确定枢轴是否是我所需要的。所有帮助将不胜感激。

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