60314

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)

Answer1:

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.

Answer2:

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?

Answer3:

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

Answer4:

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.

Answer5:

As <a href="https://stackoverflow.com/users/12349/justice" rel="nofollow">@Justice</a> 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 <a href="http://mybatis.org/" rel="nofollow">MyBatis</a>. 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.

Recommend

  • Recursively grab all data based on a parent id
  • Nearest date value
  • Which is better CTE or Inner-Selects in PostgreSQL
  • SAS: how to use the table column with special character $
  • multiple case SQL query retrieve single row as multiple column
  • Non-recursive algorithm for full permutation with repetitive elements?
  • Obtaining unstandardized factor scores from factor analysis
  • SQL convert compatibility matrix into mapping table (columns into rows)
  • Group characters of varchar field
  • R: Cleaning up a wide and untidy dataframe
  • Unzip without prompt
  • Multiplying two matrices in Java
  • Removing multiple recurring text from pandas rows`
  • Outlook 365 add-in only appears in Outlook 2013 client
  • extracting evidence of equality from match
  • How can I migrate my WP8 application to universal when it uses a local linq to sql db?
  • Unpivot table in SQL Server
  • How can I cons a list of pairs on to auto-mode-alist?
  • Read certain key from certain section of ini file (sed/awk ?)
  • Grails Packaging and Naming Conventions
  • Restrict calls to WCF service per minute, per method, per IP
  • C++ alternatives to preprocessor macro code generation?
  • Use OAuth2 for authentication + compatibility with google.appengine.api.users service
  • How does CoreLocation locate the device?
  • Sticky Service not restarting after RAM full on Xiaomi / Huawei / Lava
  • Easier way to do non-recursive list on gcs bucket outside appengine
  • iOS - Is this a task for enums?
  • DataGridView: Pass by Value or Reference?
  • How to name a 'group' check box in Adobe Reader when wanting to fill form by FDF / XFDF
  • NHibernate manually control fetching
  • Can someone please explain to me in the most layman terms how to use EventArgs?
  • Yii2: Config params vs. const/define
  • Ajax Loaded meta Tags
  • Control modification in presentation layer
  • Exchange data b/w iOS devices using Bluetooth 4.0
  • KeystoneJS: Relationships in Admin UI not updating
  • Acquiring multiple attributes from .xml file in c#
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • How can I remove ASP.NET Designer.cs files?
  • java string with new operator and a literal