How to apply the DRY principle to SQL Statements that Pivot Months

Question

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

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:

[Year],[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec],[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).

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

Answer 1

It's late and I might be missing something obvious here, but will a Months table with a row for each month help you do that?

Answer 2

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)

etc.

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.

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

Answer 3

How about using a view?

If you are always going for the same table/group of tables, a view might make sense. CAVEAT: Beware of such views, when using small sections of big tables .. the view might prevent the optimizer from doing it's job.

Answer 4

/* 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.
  */

Answer 5

As @Justice mentioned in comments, DRY generally refers to reusable code, which is much easier in your SQL client's language not in the SQL. If you're open to that option (and admittedly, you may not be), consider a datamapper like MyBatis. The Extraction into objects may be overkill for what your after, but the ability to create SQL snippets and reuse them in different queries sounds like what you're after.