CODEWITHSUNDEEP
sql-server
mfoehrer
mfoehrer

Reputation: 35

Displaying Yearly Summary data in SQL

I am trying to display my yearly summary data in a new format from the original. The current format shows the customer#, year, and total amount for each month of that here. Example.Column names are customer, 0 for the year and 1-12 for the months

customer     0         1          2           3           4          5          6             7           8           9          10           11         12
VALLERO     2014    634.150000  560.740000  254.670000  370.292500  99.225000   157.426666  358.650000  190.925000  767.515000  71.665000   587.305000  615.525000
VALLERO     2015    634.150000  560.740000  254.670000  370.292500  99.225000   157.426666  358.650000  190.925000  767.515000  71.665000   587.305000  615.525000

What I need now is to change the format to be something like this.

 customer  year1  total1   year2  total2     
 VALLERO   2014    0.00    2015    0.00

Upvotes: 0

Views: 64

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82020

If "Year #" is based on the the customer's life-time value or relative to the customer's experience. If NOT Relative just remove Partition By Customer

Easy to expand the number of columns or even make dynamic.

You'll notice a New Customer in 2017 and his Year1 is 2017

Declare @YourTable table (customer varchar(25),[0] int,[1] money,[2] money,[3] money,[4] money,[5] money,[6] money,[7] money,[8] money,[9] money,[10] money,[11] money,[12] money)
Insert Into @YourTable values
('VALLERO' ,2014 ,634.150000 ,560.740000 ,254.670000 ,370.292500 ,99.225000 ,157.426666 ,358.650000 ,190.925000 ,767.515000 ,71.665000 ,587.305000 ,615.525000),
('VALLERO' ,2015 ,634.150000 ,560.740000 ,254.670000 ,370.292500 ,99.225000 ,157.426666 ,358.650000 ,190.925000 ,767.515000 ,71.665000 ,587.305000 ,615.525000),
('NewCust' ,2017 ,500.000000 ,650.000000 ,null       ,null       ,null      ,null       ,null       ,null       ,null       ,null       ,null      ,null      )


Select Customer
      ,Year1    = max(case when YearNr= 1 then [0] end)
      ,Total1   = sum(case when YearNr= 1 then Value end)
      ,Year2    = max(case when YearNr= 2 then [0] end)
      ,Total2   = sum(case when YearNr= 2 then Value end)
From (
        Select Customer
              ,YearNr=Dense_Rank() over (Partition By Customer Order by [0])
              ,[0]
              ,Value
         From  @YourTable A
         UnPivot (Value for Item in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) u
     ) A
 Group By Customer

Returns

enter image description here

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

Assuming months columns are not null.

Try this:

select
    customer,
    max(case when year = 2014 then year end) year1,
    max(case when year = 2014 then total end) total1,
    max(case when year = 2015 then year end) year2,
    max(case when year = 2015 then total end) total2
from (
    select
        customer,
        [0] year,
        [1] + [2] + [3] + [4] + [5] + [6] + [7] + [8] + [9] + [10] + [11] + [12] total
    from t
) t
group by customer;

If they are just wrap the columns into coalesce:

select
    customer,
    max(case when year = 2014 then year end) year1,
    sum(case when year = 2014 then total end) total1,
    max(case when year = 2015 then year end) year2,
    sum(case when year = 2015 then total end) total2
from (
    select
        customer,
        [0] year,
        coalesce([1],0) + coalesce([2],0) + coalesce([3],0) + coalesce([4],0) 
        + coalesce([5],0) + coalesce([6],0) + coalesce([7],0) + coalesce([8],0)
        + coalesce([9],0) + coalesce([10],0) + coalesce([11],0) + coalesce([12],0) total
    from t
) t
group by customer;

Upvotes: 0

Related Questions