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.

What is the issue? Based on your pivot, your headers are the Fiscal Week and this should work. — Isaiah3015
– Isaiah3015, Commented Aug 30, 2017 at 15:03
What you can do in these cases is to use a dynamic column result rather than specifying each columns. Here's a sample: stackoverflow.com/questions/10404348/… — Isaiah3015
– Isaiah3015, Commented Aug 30, 2017 at 15:06
You also need put the pivot values on your SELECT .. [1], [2] ... check basic example HERE — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Aug 30, 2017 at 15:09

PeterH · Accepted Answer · 2017-08-30 15:31:06Z

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, 2017 at 15:31

PeterH

1,0404 gold badges16 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Isaiah3015 Over a year ago

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: stackoverflow.com/questions/10404348/…

PeterH Over a year ago

I did try that and it worked but I could not get it to order the weeks

Isaiah3015 Over a year ago

Now that you are one step closer, here's a solution to your ordering stackoverflow.com/questions/1122117/…

PeterH Over a year ago

@Isaiah3015 thank you, post it an answers if you want I'll accept it as correct

Isaiah3015 Over a year ago

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.

Jason A. Long · Accepted Answer · 2017-08-30 20:12:16Z

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;

Collectives™ on Stack Overflow

Pivot Data in SQL

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related