SQL GROUP BY CUBE

Question

Я хочу добавить столбец общих целей в конце таблицы, но не смог сделать это с помощью CUBE, потому что тогда он говорит, что bx недействителен. Как мне это сделать?

 DECLARE @Weeks VARCHAR(MAX) = ''
    SELECT @Weeks += (QUOTENAME([Week]) + ',') 
FROM
(Select Distinct [Week] from Fixture
) as z
SET @Weeks = LEFT(@Weeks, LEN(@Weeks) - 1)
print @Weeks

DECLARE @SQL NVARCHAR(MAX) = ''
SET @SQL = '

    select *
    from
    (
    Select Home as Team, [Week] as Weeks, [Home Score] as Goals From Fixture
    group by Home,Week, [Home Score]
    UNION
    Select Away as Team, [Week] as Weeks, [Away Score] as Goals From Fixture
    group by Away,Week, [Away Score]
    ) as A
PIVOT
(
    MAX(Goals)
For Weeks
    IN(' + @Weeks + ')
    )b


'
exec (@SQL)

Answer 1

Вы можете изменить свой внутренний запрос примерно так:

Select Home as Team, [Week] as Weeks, [Home Score] as Goals From Fixture
group by Home,Week, [Home Score]
UNION ALL
Select Away as Team, [Week] as Weeks, [Away Score] as Goals From Fixture
group by Away,Week, [Away Score]
UNION ALL
Select 'Total', NULL, SUM([Home Score]) + SUM ([Away Score]) as 'Total Goals' From Fixture

Эта логика может помочь вам.