SQL Server using UNPIVOT example

SQL Server using the UNPIVOT function in a real world type scenario.
Part of one of the projects I have worked on involved working data stored in a table with months 1 to 12 stored by year, which is not an uncommon solution, but can cause SQL statements to get bloated with dozens of case statements.
These case statements make it harder to maintain, and can also be a source of human error. Below, I have created a simple dataset set out as per a real world example, and SQL Server has a function that will turn columns into rows.

Data

UnPivID UnPivYear M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12
1 2010 1 2 3 4 5 6 7 8 9 10 11 12
1 2011 1 2 3 4 5 6 7 8 9 10 11 12
1 2012 1 2 3 4 5 6 7 8 9 10 11 12
2 2010 1 2 3 4 5 6 7 8 9 10 11 12
If you are not running below SQL Server 2012, then you will need to create this function, which is an equivalent to the DATEFROMPARTS function built in from 2012, article here: SQL Server function date from parts

SQL

CREATE FUNCTION c1bs_DateFromParts(@Year SMALLINT, @Month SMALLINT,@Day SMALLINT) RETURNS DATE AS BEGIN
DECLARE
@Date DATE=CONVERT(NVARCHAR(4),@Year)+'-'+CONVERT(NVARCHAR(2),@Month)+'-'+CONVERT(NVARCHAR(2),@Day)
RETURN @Date
END
Next we can create a temp table, and populate it with the example data above.

SQL

DECLARE @UnPiv TABLE (UnPivID INT, UnPivYear SMALLINT, M1 INT, M2 INT, M3 INT, M4 INT, M5 INT, M6 INT, M7 INT, M8 INT, M9 INT, M10 INT, M11 INT, M12 INT)
INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)
SELECT 1,2010,1,2,3,4,5,6,7,8,9,10,11,12
INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)
SELECT 1,2011,1,2,3,4,5,6,7,8,9,10,11,12
INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)
SELECT 1,2012,1,2,3,4,5,6,7,8,9,10,11,12
INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)
SELECT 2,2010,1,2,3,4,5,6,7,8,9,10,11,12

SELECT UnPivID,UnPivYear,REPLACE(col,'M','') UnPivMonth,
dbo.c1bs_DateFromParts(UnPivYear,REPLACE(col,'M',''),1) UnPivDate,
DateFromParts(UnPivYear,REPLACE(col,'M',''),1) UnPivDateFrom2012,
val
FROM @UnPiv
UNPIVOT(Val FOR colIN(M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)) unpiv
This should return a dataset as per below.

Data

UnPivID UnPivYear UnPivMonth UnPivDate UnPivDate2012 val
1 2010 1 01/01/2010 01/01/2010 1
1 2010 2 01/02/2010 01/02/2010 2
1 2010 3 01/03/2010 01/03/2010 3
1 2010 4 01/04/2010 01/04/2010 4
1 2010 5 01/05/2010 01/05/2010 5
1 2010 6 01/06/2010 01/06/2010 6
1 2010 7 01/07/2010 01/07/2010 7
1 2010 8 01/08/2010 01/08/2010 8
1 2010 9 01/09/2010 01/09/2010 9
1 2010 10 01/10/2010 01/10/2010 10
1 2010 11 01/11/2010 01/11/2010 11
1 2010 12 01/12/2010 01/12/2010 12
1 2011 1 01/01/2011 01/01/2011 1
1 2011 2 01/02/2011 01/02/2011 2
1 2011 3 01/03/2011 01/03/2011 3
1 2011 4 01/04/2011 01/04/2011 4
1 2011 5 01/05/2011 01/05/2011 5
1 2011 6 01/06/2011 01/06/2011 6
1 2011 7 01/07/2011 01/07/2011 7
1 2011 8 01/08/2011 01/08/2011 8
1 2011 9 01/09/2011 01/09/2011 9
1 2011 10 01/10/2011 01/10/2011 10
1 2011 11 01/11/2011 01/11/2011 11
1 2011 12 01/12/2011 01/12/2011 12
1 2012 1 01/01/2012 01/01/2012 1
1 2012 2 01/02/2012 01/02/2012 2
1 2012 3 01/03/2012 01/03/2012 3
1 2012 4 01/04/2012 01/04/2012 4
1 2012 5 01/05/2012 01/05/2012 5
1 2012 6 01/06/2012 01/06/2012 6
1 2012 7 01/07/2012 01/07/2012 7
1 2012 8 01/08/2012 01/08/2012 8
1 2012 9 01/09/2012 01/09/2012 9
1 2012 10 01/10/2012 01/10/2012 10
1 2012 11 01/11/2012 01/11/2012 11
1 2012 12 01/12/2012 01/12/2012 12
2 2010 1 01/01/2010 01/01/2010 1
2 2010 2 01/02/2010 01/02/2010 2
2 2010 3 01/03/2010 01/03/2010 3
2 2010 4 01/04/2010 01/04/2010 4
2 2010 5 01/05/2010 01/05/2010 5
2 2010 6 01/06/2010 01/06/2010 6
2 2010 7 01/07/2010 01/07/2010 7
2 2010 8 01/08/2010 01/08/2010 8
2 2010 9 01/09/2010 01/09/2010 9
2 2010 10 01/10/2010 01/10/2010 10
2 2010 11 01/11/2010 01/11/2010 11
2 2010 12 01/12/2010 01/12/2010 12
Ousia Logo