sql - How to pivot table by month and display top 10 by total amount only

Question

I have a dynamic dataset that returns data from today and a year back. How can I pivot this data and return only top 10 Description by NetWrittenPremium?

I know how to perform static pivot, but confused how would I do it in this case.

        ;with cte_TopClasses
    AS  ( 
            SELECT      
                        b.MonthNum,
                        b.YearNum,
                        GovClassCode + ' - ' + dda.GovClassDesc as Description,
                        ISNULL(SUM(Premium),0) as NetWrittenPremium 
            FROM        tblCalendar b 
            LEFT JOIN   ProductionReportMetrics prm ON b.YearNum = Year(prm.EffectiveDate) AND b.MonthNum=Month(prm.EffectiveDate) AND  CompanyLine = 'Arch Insurance Company'
            LEFT JOIN   [dbo].[Dynamic_Data_ArchWC] dda ON prm.QuoteGUID = dda.QuoteGuid    
            WHERE       
                        ( b.YearNum = YEAR(GETDATE())-1 and b.MonthNum >= MONTH(GETDATE())+1 ) OR 
                        ( b.YearNum = YEAR(GETDATE()) and b.MonthNum <= MONTH(GETDATE()) )      
            GROUP BY    b.YearNum ,
                        b.MonthNum, 
                        GovClassCode,   
                        dda.GovClassDesc
        )
   --here I want to pivot it
    select *
    from cte_TopClasses

Current result is 128 records.

And desirable result would be something like that:

@Nico It should be like that:

Yours is very close, just shift to 1 month ahead.

Answer 1

Maybe it's somewhat complicated. More information about dynamic pivot, you can check this post.

--begin get the ordered month name of past one year
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @startMonth AS INT

--line 1
SET @startMonth=MONTH(GETDATE())+1

WHILE(@startMonth<=12)
BEGIN 
  SET @cols= COALESCE(@cols,'')+'['+CAST(@startMonth AS varchar)+'],';
  SET @startMonth=@startMonth+1;
END

--line 2
SET @startMonth=MONTH(GETDATE())+1

DECLARE @countFlag INT
SET @countFlag=0

WHILE(@startMonth>1)
BEGIN 
  SET @countFlag=@countFlag+1;
  SET @cols= COALESCE(@cols,'')+'['+CAST(@countFlag AS varchar)+'],';
  SET @startMonth=@startMonth-1;
END

SET @cols=SUBSTRING(@cols,1,LEN(@cols)-1)
-- end

DECLARE @query NVARCHAR(MAX)

SET @cols=REPLACE(@cols,'10','October');
SET @cols=REPLACE(@cols,'11','November');
SET @cols=REPLACE(@cols,'12','December');
SET @cols=REPLACE(@cols,'1','January');
SET @cols=REPLACE(@cols,'2','February');
SET @cols=REPLACE(@cols,'3','March');
SET @cols=REPLACE(@cols,'4','April');
SET @cols=REPLACE(@cols,'5','May');
SET @cols=REPLACE(@cols,'6','June');
SET @cols=REPLACE(@cols,'7','July');
SET @cols=REPLACE(@cols,'8','August');
SET @cols=REPLACE(@cols,'9','September');

SET @query = '

SELECT 
    TOP 10
    *,
    ISNULL([October],0)+ISNULL([November],0)+ISNULL([December],0)+ISNULL([January],0)+ISNULL([February],0)+ISNULL([March],0)+ISNULL([April],0)+ISNULL([May],0)+ISNULL([June],0)+ISNULL([July],0)+ISNULL([August],0)+ISNULL([September],0) AS Total
FROM 
(
    SELECT DateName(month, DateAdd(month, MonthNum, -1))  AS [MonthName],[Description],SUM(NetWrittenPremium) AS SubTotal FROM dbo.cte_TopClasses WHERE ((YearNum-YEAR(GETDATE()))*12+MonthNum-MONTH(GETDATE()))>-12 AND ((YearNum-YEAR(GETDATE()))*12+MonthNum-MONTH(GETDATE()))<=0 GROUP BY [MonthNum],[Description]
) AS source
PIVOT
(
    SUM(SubTotal)
    FOR MonthName
    IN (' + @cols + ')
) AS pvtMonth
ORDER BY Total
'

EXEC SP_EXECUTESQL @query