CREATE VIEW dbo.SON_IKI_YILIN_SATIS_TOPLAMLARI_DETAYLI
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.YIL AS [YIL], IKI.STOCKREF AS [REF]
FROM         dbo.SON_IKI_YILIN_SATIS_TOPLAMLARI 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