SQL Server using PIVOT and UNPIVOT together

Combining the power of UNPIVOT, and then PIVOT to turn a 12 column table into a 36 table column or more
In the last 2 articles using PIVOT and SQL Server using UNPIVOT, I have written about using PIVOT and UNPIVOT separately, so please have a quick glance at these for a more detailed explanation of each section.
These functions together can be combined into a very powerful procedure. We are going to take the dataset from the UNPIVOT article, which has 12 months of data stored on a by row basis, we are the going to UNPIVOT and convert these columns into dates and finally PIVOT this into a table 36 columns wide. Lets jump into the code.

Data

CREATE TYPE c1bs_UnPivot AS TABLE(UnPivID INT,UnPivMonth DATE,UnPivData INT)
GO
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
GO
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

DECLARE @Piv AS c1bs_UnPivot
INSERT INTO @Piv
SELECT UnPivID,
dbo.c1bs_DateFromParts(UnPivYear,REPLACE(col,'M',''),1) col,
val
FROM @UnPiv
UNPIVOT (Val FOR col IN (M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)) unpiv
In the last statement we have followed the same process as in the article, declared a temporary table to insert a dummy dataset into, and then inserted the unpivoted data into a temp table.

SQL

--Complex Pivot - unknown column names
--Get Column Names for below
DECLARE @Cols NVARCHAR(MAX),@Sel NVARCHAR(MAX)
SELECT @Cols=COALESCE(@Cols+',','')+'['+CONVERT(NVARCHAR(20),UnPivMonth)+']',--Concatenate the Columns
  @Sel =COALESCE(@Sel +',','')+'ISNULL(['+CONVERT(NVARCHAR(20),UnPivMonth)+'], 0) AS ['+CONVERT(NVARCHAR(20),UnPivMonth)+']' --Concatenate the Columns into a select list
FROM @Piv
GROUP BY UnPivMonth
ORDER BY UnPivMonth

--Columns list display
SELECT @Cols ColumnList,@Sel SelectList

--Complex Pivot SQL
DECLARE @Params NVARCHAR(MAX)='@Piv c1bs_UnPivot READONLY'
DECLARE @SQL NVARCHAR(MAX)=
'SELECT [UnPivID],'+@Sel+'
FROM @Piv
PIVOT (SUM(UnPivData)
FOR UnPivMonth
IN ('+@Cols+')
) AS MyTable'

--Execute SQL
EXECUTE sp_executesql @SQL,--SQL String from above
@Params,--Parameter list
@Piv--Temp Table needs to be passed in, can only be read only
In this statement we have followed the same process as in the complex PIVOT example. Declared variables to hold our select list and column names, and injected them into a PIVOT statement to return a dataset. This has now returned a table with the 36 dates we inserted at the start. The example below only shows the first 6 columns, and the table would grow to however many dates were provided.

SQL

UnPivID01/01/201001/02/201001/03/201001/04/201001/05/201001/06/2010
1123456
2123456
Ousia Logo