代码之家  ›  专栏  ›  技术社区  ›  DavidStein

如何使用SQL2000透视/取消透视

  •  1
  • DavidStein  · 技术社区  · 16 年前

    FRECDCUST              DTRECDCUST              DTINSPECTED             DTRECDCUSTPO           DTADDEDSO                 DTSHIPPEDSUP                  DTQUOTEDCUST
    1/1/1900 12:00:00 AM    7/27/2010 12:00:00 AM   7/30/2010 12:00:00 AM   7/26/2010 12:00:00 AM   8/6/2010 12:00:00 AM    1/1/1900 12:00:00 AM    8/6/2010 12:00:00 AM
    1/1/1900 12:00:00 AM    7/27/2010 12:00:00 AM   7/30/2010 12:00:00 AM   7/26/2010 12:00:00 AM   8/6/2010 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM
    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM
    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM
    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM    1/1/1900 12:00:00 AM
    1/1/1900 12:00:00 AM    3/12/2010 12:00:00 AM   3/15/2010 12:00:00 AM   3/11/2010 12:00:00 AM   3/16/2010 12:00:00 AM   3/24/2010 12:00:00 AM   4/13/2010 12:00:00 AM
    1/1/1900 12:00:00 AM    11/6/2009 12:00:00 AM   11/6/2009 12:00:00 AM   11/3/2009 12:00:00 AM   11/9/2009 12:00:00 AM   11/20/2009 12:00:00 AM  12/7/2009 12:00:00 AM
    

    我在计算每行两个日期之间的日期差。例如,检查的天数如下:

    SELECT (CASE
              WHEN year(SYC_ext.DTRECDCUST) <> 1900
                   AND year(SYC_ext.DTINSPECTED) <> 1900
              THEN
                DATEDIFF(dd, SYC_ext.DTRECDCUST, SYC_ext.DTINSPECTED)
              ELSE
                NULL
            END)
             AS [AverageDaystoInspect]
    Blah blah...
    

    这将创建一行不同的DateDiff计算,结果如下所示:

    Product Class   Average Days to Inspect Average Days to Process SO  Average Days to Ship to Affiliate   Average Days to Quote   Average Days to Receive Ammended PO
    01  2   4   8   27  21
    01  2   4   8   27  21
    01  3   5   2   88  8
    01  3   5   2   88  8
    01  1   8   11  72  1
    01  3   2   7   27  0
    01  5   2   7   27  0
    

    因此,数据集中的每一行都以一个数字开头,该数字在本例中表示产品类别01,然后是一系列的日计算。

    我可以对这些值进行平均,并为每个产品类别给出一个平均值。但是,他们希望日计算沿着表或矩阵的左侧进行,产品类沿着顶部进行,与我在这里的正好相反。如果我在Excel中对它们进行透视,我将得到以下结果:

                              01    05     10
    Avg Days to Inspect       5      3      5
    Avg Days to Process PO    4      5      5
    Avg Days to Ship          20     18     19
    

    我希望这有道理。你知道我如何在SSRS或t-sql中执行这个操作吗?

    1 回复  |  直到 16 年前
        1
  •  0
  •   Stuart Ainsworth    15 年前

    如果您可以将第一个查询中的数据加载到中间临时表中,则可以执行此操作:

    DECLARE @t TABLE
        (
          ProductClass INT
        , InspectDays INT
        , ProcessDays INT
        , ShipDays INT
        )
    
    INSERT  INTO @t
            SELECT  1
                  , 2
                  , 4
                  , 8
            UNION ALL
            SELECT  1
                  , 4
                  , 5
                  , 2
            UNION ALL
            SELECT  5
                  , 2
                  , 4
                  , 8
            UNION ALL
            SELECT  10
                  , 3
                  , 5
                  , 2
    
    SELECT  'Avg Inspect'
          , [01] = AVG(CASE WHEN ProductClass = 1 THEN InspectDays
                       END)
          , [05] = AVG(CASE WHEN ProductClass = 5 THEN InspectDays
                       END)
          , [10] = AVG(CASE WHEN ProductClass = 10 THEN InspectDays
                       END)
    FROM    @t
    UNION ALL
    SELECT  'Avg Process'
          , [01] = AVG(CASE WHEN ProductClass = 1 THEN ProcessDays
                       END)
          , [05] = AVG(CASE WHEN ProductClass = 5 THEN ProcessDays
                       END)
          , [10] = AVG(CASE WHEN ProductClass = 10 THEN ProcessDays
                       END)
    FROM    @t