如何按月透视表并仅按总金额显示前10名 [英] How to pivot table by month and display top 10 by total amount only

问题描述

我有一个动态数据集，该数据集返回今天和一年前的数据. 如何旋转数据并仅按NetWrittenPremium返回前10个Description?

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

当前结果为128条记录.

Current result is 128 records.

理想的结果将是这样:

@Nico 应该是这样的:

@Nico It should be like that:

您非常接近，请提前1个月.

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

推荐答案

也许有点复杂.有关动态数据透视的更多信息，您可以查看这篇文章.

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

这篇关于如何按月透视表并仅按总金额显示前10名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！