How to normalize Periods or Months into Rows

​This article covers how to normalize a SQL table that has Periods or Months in columns instead of in rows.  This type of table design makes it very difficult to report out of when you need to add Periods or Months up for filter ranges.  When for example the filter range is for the last quarter up to the first quarert of the current year it gets hard because we are crossing Years.

This example uses a Table that has an Account Code, Year and amount for Periods 1 to 12

It will convert the Table into a Account Code, Year , Period and Amount as a Virtual Table (type of View) so that ranges can be better filtered using the new Period column.

Create a sample Table called PeriodBalance

select *
into PeriodBalance
from
(
select ‘100’ as [AccCode],2012 as [Year]
,10100 as [Period1],10200 as [Period2],10300 as [Period3],10400 as [Period4]
,10500 as [Period5],10600 as [Period6],10700 as [Period7],10800 as [Period8]
,10900 as [Period9],10100 as [Period10],10110 as [Period11],10120 as [Period12]

union

select ‘100’ as [AccCode],2013 as [Year]
,10100 as [Period1],10200 as [Period2],10300 as [Period3],10400 as [Period4]
,10500 as [Period5],10600 as [Period6],10700 as [Period7],10800 as [Period8]
,10900 as [Period9],10100 as [Period10],10110 as [Period11],10120 as [Period12]

union

select ‘100’ as [AccCode],2014 as [Year]
,10100 as [Period1],10200 as [Period2],10300 as [Period3],10400 as [Period4]
,10500 as [Period5],10600 as [Period6],10700 as [Period7],10800 as [Period8]
,10900 as [Period9],10100 as [Period10],10110 as [Period11],10120 as [Period12]

) X

 Preview of Creating the sample Table and the Data

 Use a SQL Join and a Case statement to normalize the Period columns into separate rows

 Create the Custom Table based on the normalized SQL statement

select
PB.[AccCode]
,PB.[Year]
,(PB.[Year] * 1000)+ PNO.PeriodNo as Period /*Use this for Filtering eg. 2014005*/
,case PNO.PeriodNo /*Roll Period values 1..12 into one column*/
when 1 then PB.Period1 when 2 then PB.Period2
when 3 then PB.Period3 when 4 then PB.Period4
when 5 then PB.Period5 when 6 then PB.Period6
when 7 then PB.Period7 when 8 then PB.Period8
when 9 then PB.Period9  when 10 then PB.Period10
when 11 then PB.Period11 when 12 then PB.Period12
else 0 end as Amount
from PeriodBalance PB
full outer join /*make a row 1..12 dataset one for each period or month*/
(select 1 as PeriodNo union select 2 union select 3 union select 4 union
select 5 union select 6 union select 7 union select 8 union
select 9 union select 10 union select 11 union select 12
) PNO ON 1=1

 Set the Data Type to Period and Length to 7 so that Period filtering logic can be used

 Query Builder showing the Normalized Virtual Table View

 Preview the Data.   You can see that Filtering on the Period now allows any range of Periods to be summed