CODEWITHSUNDEEP
sqlsql-serverpivot
sam smith
sam smith

Reputation: 107

pivot cumulative total by year, then selecting top 5

I am trying to get some pivot some year data to give me a cumulative total as the years increase and then get the top 5.

I have tried using a SUM on the total, for the years in the year column, but it doesn't appear to be increasing. The issue I think is due to some null values potentially?

The data in the table currently appears like 

Name | ApplesEaten | Year
Bob  |      2      | 2012
Bob  |      5      | 2016
Elvis|      1      | 2017
Elvis|      2      | 2012
Sam  |      8      | 2008
Elvis|      6      | 2004
Sam  |      24     | 2019
Sarah|      14     | 2015
Bob  |      6      | 2005
Rachel|      12     | 2010
Rachel|      10     | 2008
Bob  |      82     | 2006

But im aiming to get it like 

Name| 2004 | 2005 | 2006 ..... 
Bob |   0  |  6   |  88

The next issue, is getting the top 5 in total after the pivot has been done!

Upvotes: 0

Views: 75

Answers (3)

Sreenu131
Sreenu131

Reputation: 2526

Sample data

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp
;WITH CTE(Name , ApplesEaten , [Year])
AS
(
SELECT 'Bob'  ,  2      , 2012  UNION ALL
SELECT 'Bob'  ,  5      , 2016  UNION ALL
SELECT 'Elvis',  1      , 2017  UNION ALL
SELECT 'Elvis',  2      , 2012  UNION ALL
SELECT 'Sam'  ,  8      , 2008  UNION ALL
SELECT 'Elvis',  6      , 2004  UNION ALL
SELECT 'Sam'  ,  24     , 2019  UNION ALL
SELECT 'Sarah',  14     , 2015  UNION ALL
SELECT 'Bob'  ,  6      , 2005  UNION ALL
SELECT 'Rachel',  12     , 2010 UNION ALL
SELECT 'Rachel',  10     , 2008 UNION ALL
SELECT 'Bob'  ,   82     , 2006
)
SELECT Name , 
       ApplesEaten , 
       [Year]      
INTO #Temp
FROM CTE

Sql script using Dynamic Sql

DECLARE @Columns nvarchar(max),
        @IsnullColumns nvarchar(max),
        @Sql  nvarchar(max)


SELECT @Columns = STUFF((SELECT DISTINCT ', '+QUOTENAME([Year])   FROM #Temp FOR XML PATH ('')),1,1,'')


 SELECT @IsnullColumns = STUFF((SELECT DISTINCT ', '+'ISNULL(MAX('+QUOTENAME([Year])+'),''0'') AS apples_' +CAST(([Year]) AS VARCHAR(20))
                            FROM #Temp FOR XML PATH ('')),1,1,'')

SET @Sql ='SELECT TOp 5 Name,'+@IsnullColumns+'
            FROM
            (
            SELECT *,SUM(ApplesEaten) OVER(PARTITION BY Name ORDER BY [Year]) AS SumApplesEaten FROM #Temp
            ) AS PVT
            PIVOT
            (
            MAX(SumApplesEaten) FOR [Year] IN ('+@Columns+')
            ) AS PVT
            GROUP BY Name
            ORDER BY Name'
PRINT @Sql
EXEC (@Sql)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Is this what you want?

select top (5) name,
       sum(case when year <= 2005 then ApplesEaten else 0 end) as apples_2005,
       sum(case when year <= 2006 then ApplesEaten else 0 end) as apples_2006,
       . . .
       sum(case when year <= 2019 then ApplesEaten else 0 end) as apples_2019
from t
group by name
order by sum(ApplesEaten) desc

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

you can use case when

select top 5 name, max(case when year=2004  then ApplesEaten end ) [2004],
        max(case when year=2005  then ApplesEaten end  ) [2005],
        max(case when year=2006  then ApplesEaten end  ) [2006],
.......................
  from table_name group by name
  order by sum(ApplesEaten ) desc

Upvotes: 1

Related Questions