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)

By : BuddyJoe


Answers

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.

By : Chadwick


/* 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.
  */
By : BuddyJoe


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.

By : Cade Roux


This video can help you solving your question :)
By: admin