CREATE VIEW dbo.SON_IKI_YILIN_SATIS_TOPLAMLARI_DETAYLI_ORTAK
AS
SELECT 
ITM.CODE AS [OEM], 
CONCAT(CONCAT(ITM.[NAME],' '), 
ITM.[NAME3]) AS [MALZEME AÇIKLAMASI], 
GTIPCODE AS [MALZEME GRUBU], 
SPECODE4 AS [MARKA],
SPECODE2 AS [ARAÇ MARKASI],
ISNULL(UPPER(STUFF((
    SELECT ', ' + Data.Kategori
    FROM (
      SELECT CASE WHEN MAM.KAT1 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT1)) != '' THEN MAM.KAT1 ELSE NULL END AS Kategori UNION ALL
      SELECT CASE WHEN MAM.KAT2 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT2)) != '' THEN MAM.KAT2 ELSE NULL END UNION ALL
      SELECT CASE WHEN MAM.KAT3 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT3)) != '' THEN MAM.KAT3 ELSE NULL END UNION ALL
      SELECT CASE WHEN MAM.KAT4 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT4)) != '' THEN MAM.KAT4 ELSE NULL END UNION ALL
      SELECT CASE WHEN MAM.KAT5 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT5)) != '' THEN MAM.KAT5 ELSE NULL END UNION ALL
      SELECT CASE WHEN MAM.KAT6 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT6)) != '' THEN MAM.KAT6 ELSE NULL END UNION ALL
      SELECT CASE WHEN MAM.KAT7 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT7)) != '' THEN MAM.KAT7 ELSE NULL END UNION ALL
      SELECT CASE WHEN MAM.KAT8 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT8)) != '' THEN MAM.KAT8 ELSE NULL END UNION ALL
      SELECT CASE WHEN MAM.KAT9 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT9)) != '' THEN MAM.KAT9 ELSE NULL END UNION ALL
      SELECT CASE WHEN MAM.KAT10 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT10)) != '' THEN MAM.KAT10 ELSE NULL END UNION ALL
      SELECT CASE WHEN MAM.KAT11 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT11)) != '' THEN MAM.KAT11 ELSE NULL END UNION ALL
      SELECT CASE WHEN MAM.KAT12 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT12)) != '' THEN MAM.KAT12 ELSE NULL END UNION ALL
      SELECT CASE WHEN MAM.KAT13 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT13)) != '' THEN MAM.KAT13 ELSE NULL END UNION ALL
      SELECT CASE WHEN MAM.KAT14 IS NOT NULL AND LTRIM(RTRIM(MAM.KAT14)) != '' THEN MAM.KAT14 ELSE NULL END
    ) AS Data
    WHERE Data.Kategori IS NOT NULL
    FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')), '') AS [UYUMLU MODELLER], 
  IKI.SATIS AS [TOPLAM SATIS ADEDI], 
  IKI.TUTAR AS [TOPLAM SATIS TUTARI],
  IKI.STOCKREF AS [REF]
FROM
dbo.SON_IKI_YILIN_SATIS_TOPLAMLARI_ORTAK AS IKI
LEFT OUTER JOIN LG_011_ITEMS AS ITM ON ITM.LOGICALREF=IKI.STOCKREF
LEFT OUTER JOIN dbo.MALZEME_ARAC_MODELLERI AS MAM ON MAM.LOGREF = IKI.STOCKREF