如何DRY原则适用于SQL语句枢轴月(How to apply the DRY principle

我不知道别人是怎么处理这种情况？以及如何将不重复自己（DRY）原则上适用于这种情况。

我发现自己不断地摆动或T-SQL编写CASE语句呈现月为列。我通常具有某些字段，其中将包括（1）一个日期字段和（2）的值字段中。当我提出通过一个ASPX页面或本回用户报表服务，我需要有最后的最右边的14列有这样的模式：

[年]，[月]，[月]，[月]，[月]，[日]，[6月]，[月]，[月]，[月]，[月]，[月]，[减速]，[总]

其中一年是一年为一个int和所有其他领域是总结当月值字段（除[总]，这是总价值领域年度）。

我想找到一个可重复使用的方式来处理这个问题。对所有人开放的建议（T-SQL / ANSI SQL）

Answer 1:

这是不是你寻找什么，但我已经做了很多重复的UNPIVOT ，通常，我会代码生成此，以某种标准化的命名，并大量使用热膨胀系数：

WITH P AS (
    SELECT Some Data
            ,[234] -- These are stats
            ,[235]
    FROM Whatever
     )
,FINAL_UNPIVOTED AS (
    SELECT Some Data
            ,[STAT]
    FROM P
    UNPIVOT (
        STAT FOR BASE IN ([234], [235]) 
    ) AS unpvt
    WHERE STAT <> 0
)
SELECT Some Data
              ,CONVERT(int, FINAL_UNPIVOTED.[BASE]) AS [BASE]
              ,FINAL_UNPIVOTED.[STAT]
FROM FINAL_UNPIVOTED

您可以CODEGEN通过检查表或视图，并使用这样的事情：

DECLARE @sql_unpivot AS varchar(MAX)
SELECT @sql_unpivot = COALESCE(@sql_unpivot + ',', '') + COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'whatever'

而templatizing的代码：

SET @template = '
    WITH P AS (
        SELECT Some Data
                ,{@sql_unpivot}
                  FROM Whatever
         )
    ,FINAL_UNPIVOTED AS (
        SELECT Some Data
                ,[STAT]
        FROM P
        UNPIVOT (
            STAT FOR BASE IN ({@sql_unpivot}) 
        ) AS unpvt
        WHERE STAT <> 0
    )
    SELECT Some Data
                  ,CONVERT(int, FINAL_UNPIVOTED.[BASE]) AS [BASE]
                  ,FINAL_UNPIVOTED.[STAT]
    FROM FINAL_UNPIVOTED
'
SET @sql = REPLACE(@template, '{@sql_unpivot}', @sql_unpivot)

等等

当然，它可以动态运行该代码或创建和SP，你可以换出一个视图或表创建临时刚刚拿起的东西内嵌的元数据。

有关表值函数和外部见注释适用技术。

Answer 2:

这晚，我可能会丢失一些东西很明显这里，但会与行每个月帮助下表个月，你这样做吗？

Answer 3:

/* I leave year and month separate so you can use "real" Months or Fiscal Months */

CREATE FUNCTION [dbo].[fn_MonthValueColumns] 
(   
    @year int,
    @month int, 
    @measure int 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT @year as [Year],
        CASE WHEN @month = 1 THEN @measure ELSE 0 END AS [Jan], 
        CASE WHEN @month = 2 THEN @measure ELSE 0 END AS [Feb], 
        CASE WHEN @month = 3 THEN @measure ELSE 0 END AS [Mar], 
        CASE WHEN @month = 4 THEN @measure ELSE 0 END AS [Apr], 
        CASE WHEN @month = 5 THEN @measure ELSE 0 END AS [May], 
        CASE WHEN @month = 6 THEN @measure ELSE 0 END AS [Jun], 
        CASE WHEN @month = 7 THEN @measure ELSE 0 END AS [Jul], 
        CASE WHEN @month = 8 THEN @measure ELSE 0 END AS [Aug], 
        CASE WHEN @month = 9 THEN @measure ELSE 0 END AS [Sep], 
        CASE WHEN @month = 10 THEN @measure ELSE 0 END AS [Oct], 
        CASE WHEN @month = 11 THEN @measure ELSE 0 END AS [Nov], 
        CASE WHEN @month = 12 THEN @measure ELSE 0 END AS [Dec], 
        @measure AS [Total]
)

  /* 
   use a group by after your own CROSS APPLY to roll-up SUMs for the last 13 fields. 

   this function and a CROSS APPLY against 100000 records ran in 3 seconds.
   for what I am doing, I can live with that performance.
  */