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.

it just doesn't work for me i must be missing something or doing it wrong — PeterH, Aug 30 '17 at 15:02
What is the issue? Based on your pivot, your headers are the Fiscal Week and this should work. — Isaiah3015, Aug 30 '17 at 15:03
i want the fiscal week as the columns across the top, then the branch numbers displayed down the left, — PeterH, Aug 30 '17 at 15:05
What you can do in these cases is to use a dynamic column result rather than specifying each columns. Here's a sample: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Isaiah3015, Aug 30 '17 at 15:06
You also need put the pivot values on your `SELECT .. [1], [2] ...` check basic example [**HERE**](https://technet.microsoft.com/es-es/library/ms177410(v=sql.105).aspx) — Juan Carlos Oropeza, Aug 30 '17 at 15:09
Guy Thank You very much, I have something that is now working, just need to work out how to order it !! — PeterH, Aug 30 '17 at 15:12
You only list `MyData.BranchNo` on your column list. How do you expect the other columns to show? — Eric, Aug 30 '17 at 15:51

score 2 · Accepted Answer · answered Aug 30 '17 at 15:31

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.

answered Aug 30 '17 at 15:31

PeterH

975
2
14
36

As I answered on the top comment, use a dynamic pivot so you dont need to write FiscalWeek IN 1-52. Here's a solution: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – Isaiah3015 Aug 30 '17 at 17:48
I did try that and it worked but I could not get it to order the weeks – PeterH Aug 30 '17 at 18:02
Now that you are one step closer, here's a solution to your ordering https://stackoverflow.com/questions/1122117/sql-dynamic-pivot-how-to-order-columns – Isaiah3015 Aug 30 '17 at 18:05
@Isaiah3015 thank you, post it an answers if you want I'll accept it as correct – PeterH Aug 30 '17 at 19:01
Thanks man. No need for my own answer. Edit your answer here and accept it (you can accept your own answer) with the code of dynamic pivot and dynamic order. I'm glad it worked out. – Isaiah3015 Aug 30 '17 at 19:18

score 1 · Answer 2 · answered Aug 30 '17 at 20:12

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;

Pivot Data in SQL

2 Answers2