Pivot Table NULL Sorunu Hk

Question

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;

Answer 1

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;

Answer 2

@Ç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;