Pivot Data in SQL

Question

I have the below which gives me SalesExVAT, by BranchNo and by FiscalWeek

there is only 1 record per branch for each week:

Select 
        sa.BranchNo
        ,sa.FiscalWeek
        ,sa.SalesExVAT
    From
        dbo.SalesAggregateWeek sa
    Where 
        sa.FiscalYear = 2016

I wanted to display this in a Pivoted Format

I have tried the below,

Select 
    MyData.BranchNo

From
    (Select 
        sa.BranchNo
        ,sa.FiscalWeek
        ,sa.SalesExVAT
    From
        dbo.SalesAggregateWeek sa
    Where 
        sa.FiscalYear = 2016) MyData
Pivot
( sum(MyData.salesexvat)
  For 
    MyData.FiscalWeek In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26
                    ,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52)  )

The desired out come would have the FiscalWeek as the headers along the top, the BranchNo displayed down the left, and SalesExVAT info as the data.

Any ideas on what I must do to correct my code are welcome as I've not used PIVOT yet.

Answer 1

Here an option using a dynamic cross tab pivot...

DECLARE 
    @WeekColumns VARCHAR(8000) = '',
    @sql VARCHAR(8000) = '',
    @DeBug BIT = 1;     -- change to 0 to execute & 1 to print.

SELECT TOP 52
    @WeekColumns = CONCAT(@WeekColumns, ',
    [',t.n, '] = SUM(CASE WHEN sa.FiscalWeek = ', t.n, ' THEN sa.SalesExVAT ELSE 0 END)')
FROM 
    dbo.tfn_Tally(52, 1) t;

SET @sql = CONCAT('
SELECT
    sa.BranchNo', 
    @WeekColumns, '
FROM
    dbo.SalesAggregateWeek sa
WHERE
    sa.FiscalYear = 2016;')

IF @DeBug = 1
BEGIN
    PRINT(@sql);
END;
ELSE
BEGIN
    EXEC(@sql);
END;

The print output...

SELECT
    sa.BranchNo,
    [1] = SUM(CASE WHEN sa.FiscalWeek = 1 THEN sa.SalesExVAT ELSE 0 END),
    [2] = SUM(CASE WHEN sa.FiscalWeek = 2 THEN sa.SalesExVAT ELSE 0 END),
    [3] = SUM(CASE WHEN sa.FiscalWeek = 3 THEN sa.SalesExVAT ELSE 0 END),
    [4] = SUM(CASE WHEN sa.FiscalWeek = 4 THEN sa.SalesExVAT ELSE 0 END),
    [5] = SUM(CASE WHEN sa.FiscalWeek = 5 THEN sa.SalesExVAT ELSE 0 END),
    [6] = SUM(CASE WHEN sa.FiscalWeek = 6 THEN sa.SalesExVAT ELSE 0 END),
    [7] = SUM(CASE WHEN sa.FiscalWeek = 7 THEN sa.SalesExVAT ELSE 0 END),
    [8] = SUM(CASE WHEN sa.FiscalWeek = 8 THEN sa.SalesExVAT ELSE 0 END),
    [9] = SUM(CASE WHEN sa.FiscalWeek = 9 THEN sa.SalesExVAT ELSE 0 END),
    [10] = SUM(CASE WHEN sa.FiscalWeek = 10 THEN sa.SalesExVAT ELSE 0 END),
    [11] = SUM(CASE WHEN sa.FiscalWeek = 11 THEN sa.SalesExVAT ELSE 0 END),
    [12] = SUM(CASE WHEN sa.FiscalWeek = 12 THEN sa.SalesExVAT ELSE 0 END),
    [13] = SUM(CASE WHEN sa.FiscalWeek = 13 THEN sa.SalesExVAT ELSE 0 END),
    [14] = SUM(CASE WHEN sa.FiscalWeek = 14 THEN sa.SalesExVAT ELSE 0 END),
    [15] = SUM(CASE WHEN sa.FiscalWeek = 15 THEN sa.SalesExVAT ELSE 0 END),
    [16] = SUM(CASE WHEN sa.FiscalWeek = 16 THEN sa.SalesExVAT ELSE 0 END),
    [17] = SUM(CASE WHEN sa.FiscalWeek = 17 THEN sa.SalesExVAT ELSE 0 END),
    [18] = SUM(CASE WHEN sa.FiscalWeek = 18 THEN sa.SalesExVAT ELSE 0 END),
    [19] = SUM(CASE WHEN sa.FiscalWeek = 19 THEN sa.SalesExVAT ELSE 0 END),
    [20] = SUM(CASE WHEN sa.FiscalWeek = 20 THEN sa.SalesExVAT ELSE 0 END),
    [21] = SUM(CASE WHEN sa.FiscalWeek = 21 THEN sa.SalesExVAT ELSE 0 END),
    [22] = SUM(CASE WHEN sa.FiscalWeek = 22 THEN sa.SalesExVAT ELSE 0 END),
    [23] = SUM(CASE WHEN sa.FiscalWeek = 23 THEN sa.SalesExVAT ELSE 0 END),
    [24] = SUM(CASE WHEN sa.FiscalWeek = 24 THEN sa.SalesExVAT ELSE 0 END),
    [25] = SUM(CASE WHEN sa.FiscalWeek = 25 THEN sa.SalesExVAT ELSE 0 END),
    [26] = SUM(CASE WHEN sa.FiscalWeek = 26 THEN sa.SalesExVAT ELSE 0 END),
    [27] = SUM(CASE WHEN sa.FiscalWeek = 27 THEN sa.SalesExVAT ELSE 0 END),
    [28] = SUM(CASE WHEN sa.FiscalWeek = 28 THEN sa.SalesExVAT ELSE 0 END),
    [29] = SUM(CASE WHEN sa.FiscalWeek = 29 THEN sa.SalesExVAT ELSE 0 END),
    [30] = SUM(CASE WHEN sa.FiscalWeek = 30 THEN sa.SalesExVAT ELSE 0 END),
    [31] = SUM(CASE WHEN sa.FiscalWeek = 31 THEN sa.SalesExVAT ELSE 0 END),
    [32] = SUM(CASE WHEN sa.FiscalWeek = 32 THEN sa.SalesExVAT ELSE 0 END),
    [33] = SUM(CASE WHEN sa.FiscalWeek = 33 THEN sa.SalesExVAT ELSE 0 END),
    [34] = SUM(CASE WHEN sa.FiscalWeek = 34 THEN sa.SalesExVAT ELSE 0 END),
    [35] = SUM(CASE WHEN sa.FiscalWeek = 35 THEN sa.SalesExVAT ELSE 0 END),
    [36] = SUM(CASE WHEN sa.FiscalWeek = 36 THEN sa.SalesExVAT ELSE 0 END),
    [37] = SUM(CASE WHEN sa.FiscalWeek = 37 THEN sa.SalesExVAT ELSE 0 END),
    [38] = SUM(CASE WHEN sa.FiscalWeek = 38 THEN sa.SalesExVAT ELSE 0 END),
    [39] = SUM(CASE WHEN sa.FiscalWeek = 39 THEN sa.SalesExVAT ELSE 0 END),
    [40] = SUM(CASE WHEN sa.FiscalWeek = 40 THEN sa.SalesExVAT ELSE 0 END),
    [41] = SUM(CASE WHEN sa.FiscalWeek = 41 THEN sa.SalesExVAT ELSE 0 END),
    [42] = SUM(CASE WHEN sa.FiscalWeek = 42 THEN sa.SalesExVAT ELSE 0 END),
    [43] = SUM(CASE WHEN sa.FiscalWeek = 43 THEN sa.SalesExVAT ELSE 0 END),
    [44] = SUM(CASE WHEN sa.FiscalWeek = 44 THEN sa.SalesExVAT ELSE 0 END),
    [45] = SUM(CASE WHEN sa.FiscalWeek = 45 THEN sa.SalesExVAT ELSE 0 END),
    [46] = SUM(CASE WHEN sa.FiscalWeek = 46 THEN sa.SalesExVAT ELSE 0 END),
    [47] = SUM(CASE WHEN sa.FiscalWeek = 47 THEN sa.SalesExVAT ELSE 0 END),
    [48] = SUM(CASE WHEN sa.FiscalWeek = 48 THEN sa.SalesExVAT ELSE 0 END),
    [49] = SUM(CASE WHEN sa.FiscalWeek = 49 THEN sa.SalesExVAT ELSE 0 END),
    [50] = SUM(CASE WHEN sa.FiscalWeek = 50 THEN sa.SalesExVAT ELSE 0 END),
    [51] = SUM(CASE WHEN sa.FiscalWeek = 51 THEN sa.SalesExVAT ELSE 0 END),
    [52] = SUM(CASE WHEN sa.FiscalWeek = 52 THEN sa.SalesExVAT ELSE 0 END)
FROM
    dbo.SalesAggregateWeek sa
WHERE
    sa.FiscalYear = 2016;

Answer 2

Final Query Looks Like:

Select *

From 
    (Select 
            sa.BranchNo
            ,sa.FiscalWeek
            ,sa.SalesExVAT
        From
            dbo.SalesAggregateWeek sa
        Where 
            sa.FiscalYear = 2016)  P
Pivot 
        (Sum (SalesExVAT)
        For FiscalWeek In       
        (   [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],
            [18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],
            [33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],
            [48],[49],[50],[51],[52] )
        ) As pvt    ;

Key points:

The FiscalWeek Values all had to be in [Square Brackets]

The Pivot had to use an alias 'As pvt' and be finished with ;

If anyone knows a way I could have wrote FiscalWeek Between 1 And 52 rather than state all the weeks please comment your answer.