Как получить общую сумму для каждого Описания и упорядочить ее в динамическом пивоте

Question

Мне нужно отобразить только 10 лучших строк на основе общего sum для каждого Class и заказать его по общей сумме desc. Вот чего я пытаюсь добиться:

Так что у меня есть динамический круг, но я не могу понять, как получить total столбец для каждой строки, а затем отображать только первые 10 Total колонка.

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

SELECT @cols = STUFF(
                    (SELECT distinct ',' + QUOTENAME([YearMonth]) 
                    FROM #MyTable
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
--print @cols

SELECT @query = 
'
        ;with cte_TopClasses
AS  ( 
        SELECT      
                    CAST(b.MonthNum as varchar(2)) + ''/''+ CAST(b.YearNum as varchar(4)) as YearMonth,
                    b.YearNum,
                    b.MonthNum,
                    GovClassCode + ''- '' + dda.GovClassDesc as Class,
                    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()) )  
                    AND GovClassCode + '' - '' + dda.GovClassDesc IS NOT NULL   
        GROUP BY    b.YearNum ,
                    b.MonthNum, 
                    GovClassCode,   
                    dda.GovClassDesc
    )
SELECT * FROM
(SELECT     
    [YearMonth], 
    Class,
    NetWrittenPremium
FROM cte_TopClasses)X 

PIVOT 
(
    sum(NetWrittenPremium)
    for [YearMonth] in (' + @cols + ')
) P
'

EXEC SP_EXECUTESQL @query

на выходе получается 73 строки:

ОБНОВЛЕНИЕ: использование GROUPING SETS

ОБНОВЛЕНИЕ: Вот как данные выглядят до PIVOT:

Answer 1

Как насчет этого:

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

SELECT @cols = STUFF(
                    (SELECT distinct ',' + QUOTENAME([YearMonth]) 
                    FROM #MyTable
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'') + ',' + '[Total]'
--print @cols

SELECT @query = 
'
        ;with cte_TopClasses
AS  ( 
        SELECT      
                    ISNULL(CAST(b.MonthNum as varchar(2)) + ''/''+ CAST(b.YearNum as varchar(4)), ''Total'') as YearMonth,
                    b.YearNum,
                    b.MonthNum,
                    GovClassCode + ''- '' + dda.GovClassDesc as Class,
                    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()) )  
                    AND GovClassCode + '' - '' + dda.GovClassDesc IS NOT NULL   
        GROUP BY    GROUPING SETS
        (
                (
                    b.YearNum ,
                    b.MonthNum, 
                    GovClassCode,   
                    dda.GovClassDesc
                )
                , 
                (
                    GovClassCode
                   ,dda.GovClassDesc
                )
        )
    )
SELECT * FROM
(SELECT     
    [YearMonth], 
    Class,
    NetWrittenPremium
FROM cte_TopClasses)X 

PIVOT 
(
    sum(NetWrittenPremium)
    for [YearMonth] in (' + @cols + ')
) P
'

EXEC SP_EXECUTESQL @query

Идея состоит в том, чтобы добавить Total столбец в T-SQL с помощью GROUPING SETS