Pivoting on Date Category
Here are examples of both a STATIC and DYNAMIC PIVOT

First, let's create a test table

--Create demo temp table
IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL
    DROP TABLE #TestTable
Create Table #TestTable (
    PayDay Date
    ,Category varchar(20)
    ,Average DECIMAL(11, 2)
    );

--insert some test data
insert into #TestTable values ('2016-12-01','Entertainment',32.32);
insert into #TestTable values ('2016-12-01','Extra Expenses',39.18);
insert into #TestTable values ('2016-12-01','Shopping',591.13);
insert into #TestTable values ('2016-12-15','Entertainment',34.34);
insert into #TestTable values ('2016-12-15','Extra Expenses',42.73);
insert into #TestTable values ('2016-12-15','Shopping',577.69);
insert into #TestTable values ('2016-12-29','Entertainment',34.34);
insert into #TestTable values ('2016-12-29','Extra Expenses',53.22);
insert into #TestTable values ('2016-12-29','Shopping',580.08);


Example of STATIC PIVOT where the Category column has a fixed set of known values.

--Example of STATIC pivot if the Categories are fixed
;
WITH cte_TestTable
AS (
    SELECT PayDay
        ,Category
        ,Average
    FROM #TestTable
    )
SELECT *
FROM cte_TestTable
PIVOT(SUM(Average) FOR Category IN (
            [Entertainment]
            ,[Extra Expenses]
            ,[Shopping]
            )) AS pvt
ORDER BY Payday;




Now, let's add another row to the test table with a new category

--Insert a new category for use in a DYNAMIC PIVOT
insert into #TestTable values ('2016-12-29','Bar Hopping',1000.08);


And now an example of a DYNAMIC PIVOT to handle variable categories

--Example of DYNAMIC PIVOT
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Category) 
                    from #TestTable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT PayDay, ' + @cols + ' from 
             (
                select *
                from #TestTAble
            ) x
            pivot 
            (
                SUM(Average)
                for Category in (' + @cols + ')
            ) p '

execute(@query)