如何将DRY原理应用于枢纽月份的SQL语句 [英] How to apply the DRY principle to SQL Statements that Pivot Months

问题描述

我想知道其他人如何处理这种情况……以及如何将不要重复自己的"(DRY)原则应用于这种情况.

I'm wondering how others handle this situation... and how to apply the Don't Repeat Yourself (DRY) principle to this situation.

我发现自己经常在T-SQL中使用PIVOTing或编写CASE语句以将Months表示为列.我通常有一些字段，其中将包括(1)日期字段和(2)值字段.当我通过ASPX页面或Reporting Services将其呈现给用户时，我需要在最右边的14列中具有此模式:

I find myself constantly PIVOTing or writing CASE statements in T-SQL to present Months as columns. I generally have some fields that will include (1) a date field and (2) a value field. When I present this back to a user through an ASPX page or Reporting Services I need to have the last right-most 14 columns to have this pattern:

[年份]，[Jan]，[Feb]，[Mar]，[Apr]，[May]，[Jun]，[Jul]，[Aug]，[Sep]，[Oct]，[Nov] ，[Dec]，[Total]

[Year],[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec],[Total]

年份是整数年份 其他所有字段都是该月的总和(不包括[Total]，这是该年的总价值).

Where year is the year as an int and every other field is the value field summed for that month (except for [Total] which is the total value field for the year).

我想找到一种可重复使用的方式来处理此问题.打开所有建议(T-SQL/ANSI SQL)

I'd like to find one re-usable way to handle this. Open to all suggestions (T-SQL / ANSI SQL)

推荐答案

这并非您所要的，但我已经做了很多重复的UNPIVOT，通常，我会进行代码生成通过某种标准化的命名方式，并大量使用CTE:

This isn't exactly what you're looking for, but I've done a lot of repetitive UNPIVOT, and typically, I would code-gen this, with some kind of standardized naming and use CTEs heavily:

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

您可以通过检查表或视图并使用如下代码来进行代码生成:

You can codegen by inspecting a table or view and using something like this:

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

然后对代码进行模板化:

And templatizing the code:

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，并且可以换出临时创建的视图或表，而只是为某些内联控件获取元数据.

Of course, it's possible to run this code dynamically or create and SP and you can swap out a view or table you created temporarily just to pick up metadata for something inline.

请参阅有关表值函数和OUTER APPLY技术的注释.

See comments regarding table-valued functions and OUTER APPLY technique.

这篇关于如何将DRY原理应用于枢纽月份的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！