CREATE VIEW dbo.AYLARA_GORE_SEHIRLERE_YAPILAN_SATIS_YANYANA
AS
WITH OriginalData AS (SELECT        CITY, TYPE, SUM(OCAK) AS OCAK, SUM(SUBAT) AS SUBAT, SUM(MART) AS MART, SUM(NISAN) AS NISAN, SUM(MAYIS) AS MAYIS, SUM(HAZIRAN) AS HAZIRAN, SUM(TEMMUZ) AS TEMMUZ, 
                                                                           SUM(AGUSTOS) AS AGUSTOS, SUM(EYLÜL) AS EYLUL, SUM(EKIM) AS EKIM, SUM(KASIM) AS KASIM, SUM(ARALIK) AS ARALIK, SUM(OCAK) + SUM(SUBAT) + SUM(MART) + SUM(NISAN) + SUM(MAYIS) 
                                                                           + SUM(HAZIRAN) + SUM(TEMMUZ) + SUM(AGUSTOS) + SUM(EYLÜL) + SUM(EKIM) + SUM(KASIM) + SUM(ARALIK) AS TOPLAM
                                                  FROM            dbo.SATIS_RAPOR_AYLIK_SEHIR_TOPLAMLARI
                                                  WHERE        (CITY <> '')
                                                  GROUP BY CITY, TYPE), CityDataWithEmptyRows AS
    (SELECT        CITY, TYPE, OCAK, SUBAT, MART, NISAN, MAYIS, HAZIRAN, TEMMUZ, AGUSTOS, EYLUL, EKIM, KASIM, ARALIK, TOPLAM
      FROM            OriginalData AS OriginalData_2
      UNION ALL
      SELECT DISTINCT 
                               CITY, NULL AS TYPE, NULL AS OCAK, NULL AS SUBAT, NULL AS MART, NULL AS NISAN, NULL AS MAYIS, NULL AS HAZIRAN, NULL AS TEMMUZ, NULL AS AGUSTOS, NULL AS EYLUL, NULL AS EKIM, NULL AS KASIM, NULL 
                               AS ARALIK, NULL AS TOPLAM
      FROM            OriginalData AS OriginalData_1)
    SELECT        TOP (100) PERCENT CITY, TYPE, OCAK, SUBAT, MART, NISAN, MAYIS, HAZIRAN, TEMMUZ, AGUSTOS, EYLUL, EKIM, KASIM, ARALIK, TOPLAM
     FROM            CityDataWithEmptyRows AS CityDataWithEmptyRows_1