none
Pivot Table NULL Sorunu Hk RRS feed

  • Soru

  • Merhaba ;


    2 türlüde ne yaptıysam  çözemedim..

    ISNULL(SUM)     VE  SUM(ISNULL)    COALESCE   hepsinde sonuc null dönüyor.

    gridde sıfırlama yapıyorum sorun yok ama sql ile 0  görmek istiyorum..

    SET LANGUAGE Turkish;
            SELECT  *
            FROM    ( SELECT    ISNULL(st.AMOUNT, 0) AS miktar ,
                                DATENAME(mm, DATE_) AS ay ,
                                ITM.NAME ,
                                lc.DEFINITION_
                      FROM      LG_001_01_STLINE AS st
                                INNER JOIN LG_001_CLCARD lc ON lc.LOGICALREF = st.CLIENTREF
                                INNER JOIN dbo.LG_001_ITEMS AS ITM ON ITM.LOGICALREF = st.STOCKREF
                      WHERE     st.TRCODE <> 1
                    ) tll PIVOT
     ( SUM(tll.miktar) FOR ay IN ( [Ocak], [Şubat], [Mart], [Nisan], [Mayıs],
                                   [Haziran], [Temmuz], [Ağustos], [Eylül], [Ekim],
                                  [Kasım], [Aralık] ) ) p;


    • Düzenleyen Ömer_ 22 Şubat 2016 Pazartesi 11:45
    22 Şubat 2016 Pazartesi 11:45

Yanıtlar

  • Tum o aylar icin deger yok demek ki. Ben sana ornek Northwind database ile ne demek istedigimi gostereyim, sen kendininkine uyarla:

    Bu seninki:

    SELECT * FROM 
    	(
    		SELECT  oe.EmployeeID, oe.LastName, oe.[FirstName], o.ShipCountry AS Country,
    				COALESCE(OD.Quantity * OD.UnitPrice, 0) AS QU
    		FROM    Employees oe
    		LEFT JOIN Orders O ON O.EmployeeID = oe.EmployeeID
    		LEFT JOIN [Order Details] OD ON OD.OrderID = O.OrderID 
    	) AS T
    	PIVOT (SUM(QU) FOR [Country] IN ([Argentina], [Austria], [Belgium], [Brazil], 
    	[Canada], [Denmark], [Finland], [France], [Germany], 
    	[Ireland], [Italy], [Mexico], [Norway], [Poland], 
    	[Portugal], [Spain], [Sweden], [Switzerland], [UK], [USA], [Venezuela])) AS PVT
    	ORDER BY 1;

    Bu da biraz modifiye hali:

    SELECT * from
    	(
    		SELECT  oe.EmployeeID, oe.LastName, oe.FirstName, 
    			oe.ShipCountry AS Country, COALESCE(OD.Quantity * OD.UnitPrice, 0) AS QU
    		FROM    (
    				  SELECT  EmployeeID, LastName, FirstName, ShipCountry
    				  FROM    (
    							SELECT DISTINCT
    									ShipCountry
    							FROM    Orders
    						  ) o ,
    						  Employees
    				) oe
    		LEFT JOIN Orders O ON O.EmployeeID = oe.EmployeeID AND
    							  [oe].[ShipCountry] = [O].[ShipCountry]
    		LEFT JOIN [Order Details] OD ON OD.OrderID = O.OrderID 
    	) AS T
    	PIVOT (SUM(QU) FOR [Country] IN ([Argentina], [Austria], [Belgium], [Brazil], 
    	[Canada], [Denmark], [Finland], [France], [Germany], 
    	[Ireland], [Italy], [Mexico], [Norway], [Poland], 
    	[Portugal], [Spain], [Sweden], [Switzerland], [UK], [USA], [Venezuela])) AS PVT
    	ORDER BY 1;
    

    • Yanıt Olarak İşaretleyen Ömer_ 23 Şubat 2016 Salı 06:48
    22 Şubat 2016 Pazartesi 16:27

Tüm Yanıtlar

  • Tum o aylar icin deger yok demek ki. Ben sana ornek Northwind database ile ne demek istedigimi gostereyim, sen kendininkine uyarla:

    Bu seninki:

    SELECT * FROM 
    	(
    		SELECT  oe.EmployeeID, oe.LastName, oe.[FirstName], o.ShipCountry AS Country,
    				COALESCE(OD.Quantity * OD.UnitPrice, 0) AS QU
    		FROM    Employees oe
    		LEFT JOIN Orders O ON O.EmployeeID = oe.EmployeeID
    		LEFT JOIN [Order Details] OD ON OD.OrderID = O.OrderID 
    	) AS T
    	PIVOT (SUM(QU) FOR [Country] IN ([Argentina], [Austria], [Belgium], [Brazil], 
    	[Canada], [Denmark], [Finland], [France], [Germany], 
    	[Ireland], [Italy], [Mexico], [Norway], [Poland], 
    	[Portugal], [Spain], [Sweden], [Switzerland], [UK], [USA], [Venezuela])) AS PVT
    	ORDER BY 1;

    Bu da biraz modifiye hali:

    SELECT * from
    	(
    		SELECT  oe.EmployeeID, oe.LastName, oe.FirstName, 
    			oe.ShipCountry AS Country, COALESCE(OD.Quantity * OD.UnitPrice, 0) AS QU
    		FROM    (
    				  SELECT  EmployeeID, LastName, FirstName, ShipCountry
    				  FROM    (
    							SELECT DISTINCT
    									ShipCountry
    							FROM    Orders
    						  ) o ,
    						  Employees
    				) oe
    		LEFT JOIN Orders O ON O.EmployeeID = oe.EmployeeID AND
    							  [oe].[ShipCountry] = [O].[ShipCountry]
    		LEFT JOIN [Order Details] OD ON OD.OrderID = O.OrderID 
    	) AS T
    	PIVOT (SUM(QU) FOR [Country] IN ([Argentina], [Austria], [Belgium], [Brazil], 
    	[Canada], [Denmark], [Finland], [France], [Germany], 
    	[Ireland], [Italy], [Mexico], [Norway], [Poland], 
    	[Portugal], [Spain], [Sweden], [Switzerland], [UK], [USA], [Venezuela])) AS PVT
    	ORDER BY 1;
    

    • Yanıt Olarak İşaretleyen Ömer_ 23 Şubat 2016 Salı 06:48
    22 Şubat 2016 Pazartesi 16:27
  • @Çetin bey  dogrudur  null gelen tablolarda veri yok ama ben oraya null yerine 0  degeri yazdırmak istiyorum..

    çözüm :

    ilk select den sonra

    ISNULL(Ocak,0) as [Ocak],

    ----

    SET LANGUAGE Turkish;
            SELECT  *
            FROM    ( SELECT    ISNULL(st.AMOUNT, 0) AS miktar ,
                                DATENAME(mm, DATE_) AS ay ,
                                ITM.NAME ,
                                lc.DEFINITION_
                      FROM      LG_001_01_STLINE AS st
                                INNER JOIN LG_001_CLCARD lc ON lc.LOGICALREF = st.CLIENTREF
                                INNER JOIN dbo.LG_001_ITEMS AS ITM ON ITM.LOGICALREF = st.STOCKREF
                      WHERE     st.TRCODE <> 1
                    ) tll PIVOT
     ( SUM(tll.miktar) FOR ay IN ( [Ocak], ubat], [Mart], [Nisan], [Mayıs],
                                   [Haziran], [Temmuz], [Ağustos], [Eylül], [Ekim],
                                  [Kasım], [Aralık] ) ) p;


    • Düzenleyen Ömer_ 26 Şubat 2016 Cuma 15:20
    23 Şubat 2016 Salı 06:48