Sql-server – Pivoting on Date Category

pivotsql server

I have some data in a DB, which I'm trying to get out into a spreadsheet programmatically. The data is a dollar value for a number of categories. Each fortnight, each category has a value added.

The data looks like this (simplified):

PayDay      Category        Average
2016-12-01  Entertainment   $32.32
2016-12-01  Extra Expenses  $39.18
2016-12-01  Shopping        $591.13
2016-12-15  Entertainment   $34.34
2016-12-15  Extra Expenses  $42.73
2016-12-15  Shopping        $577.69
2016-12-29  Entertainment   $34.34
2016-12-29  Extra Expenses  $53.22
2016-12-29  Shopping        $580.08

But I want to be able to select directly from the DB as:

PayDay      Entertainment   Extra Expenses  Shopping
2016-12-01  $32.32          $39.18          $591.13
2016-12-15  $34.34          $42.73          $577.69
2016-12-29  $34.34          $53.22          $580.08

Been trying, but I can't get my head around it.

Best Answer

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;

enter image description here

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)

enter image description here