Database

SELECT     TOP (100) PERCENT dbo.CustomerMaster.BSACustomerKey, dbo.ContactMaster.ContactName, dbo.ContactMaster.BusinessPhone, dbo.ContactMaster.MobilePhone, 
                      dbo.ContactMaster.Email, dbo.ContactMaster.FirstName, dbo.ContactMaster.LastName, dbo.ContactMaster.EmergencyMaintenance, 
                      dbo.ContactMaster.NormalMaintenance, dbo.ContactMaster.NetworkAbuse, dbo.ContactMaster.IssuedSwipeCard, dbo.ContactMaster.CardNumber, 
                      dbo.AddressList.CustomerAddressLine1, dbo.AddressList.CustomerAddressLine2, dbo.AddressList.CustomerAddressLine3, dbo.AddressList.CustomerPostBox, 
                      dbo.CityEnumerator.City, dbo.StateEnumerator.State, dbo.AddressList.CustomerCounty, dbo.AddressList.CustomerCountry, dbo.AddressList.CustomerZipcode, 
                      dbo.ContactMaster.BmcCustKey, CASE WHEN dbo.ContactMaster.Role = 1 THEN 1 ELSE 0 END AS IsAdministrator, 
                      CASE WHEN dbo.ContactMaster.Role = 6 THEN 1 ELSE 0 END AS IsDecisionMaker, 
                      CASE WHEN dbo.ContactMaster.Role = 5 THEN 1 ELSE 0 END AS IsAccountingAP
FROM         dbo.ContactMaster INNER JOIN
                      dbo.CustomerMaster ON dbo.ContactMaster.CompanyID = dbo.CustomerMaster.CustomerIdentity LEFT OUTER JOIN
                      dbo.AddressList ON dbo.ContactMaster.ContactAddress = dbo.AddressList.AddressIdentity LEFT OUTER JOIN
                      dbo.CityEnumerator ON dbo.AddressList.CustomerCity = dbo.CityEnumerator.CityIdentity LEFT OUTER JOIN
                      dbo.StateEnumerator ON dbo.AddressList.CustomerStateorProvince = dbo.StateEnumerator.StateIdentity
WHERE     (dbo.CustomerMaster.CustomerStatus = 1) AND (dbo.ContactMaster.Status = 1)
ORDER BY dbo.CustomerMaster.BSACustomerKey

DECLARE @LineKgs Table(
           ID Int, PLine VarChar(7),kgs Decimal(18,7), TDate nVarChar(10))

INSERT INTO @LineKgs
  SELECT  f.Factory_No + '/' + CAST(pl.ProdLine_No As VarChar), pl.ProdLine_ID,
          mt.Qty, CAST(month(mt.TransDate) AS VArChar(10))
  
  FROM Production pr
    LEFT OUTER JOIN ProdLine pl
      ON pl.ProdLine_ID = pr.ProdLine_ID
    JOIN MaterialTransaction mt
      ON pr.Prodn_ID = mt.Prodn_ID
    JOIN Factory f
      ON pl.Factory_ID = f.Factory_ID
   WHERE mt.TransDate BETWEEN '1/1/2010' AND '12/31/2010'   
 
 SELECT ID, PLine, [1] As Jan, [2] As Feb, [3] As Mar, [4] As Apr,
            [5] As May, [6] As Jun, [7] As Jul, [8] As Aug,
            [9] As Sep, [10] As Oct, [11] As Nov, [12] as Dec
     FROM( SELECT ID, PLine, kgs, TDate
         
      FROM @LineKgs) p2
      PIVOT(SUM(kgs)
         FOR TDate In ([1], [2],[3],[4],[5],[6],
                       [7],[8],[9],[10],[11],[12])) As P

ID	PLine	Jan	Feb	Mar      ...  
=============================================    
1	Fac1/1	8497	60012	19503    ...
2	Fac1/2	104772  152379	33795
3	Fac1/3	76338	113653	40742
4	Fac1/4	197339	333348	118314
5	Fac1/5	200986	363314	106225
6	Fac1/6	35048	189619	44345
7	Fac1/7	45632	133981	52921
8	Fac1/8	58387	185468	39916
9	Fac1/9	34625	76026	69479
10	Fac2/1	420	4150	828
19	Fac2/10	10816	25213	2681
20	Fac2/11	13680	30765	4597
21	Fac2/12	10576	NULL	19068
11	Fac2/2	251	333	NULL
12	Fac2/3	433	1536	NULL
...

ID	PLine	Jan	Feb	Mar      ...  
=============================================    
1	Fac1/1	8497	60012	19503    ...
2	Fac1/2	104772  152379	33795
3	Fac1/3	76338	113653	40742
4	Fac1/4	197339	333348	118314
5	Fac1/5	200986	363314	106225
6	Fac1/6	35048	189619	44345
7	Fac1/7	45632	133981	52921
8	Fac1/8	58387	185468	39916
9	Fac1/9	34625	76026	69479
10	Fac2/1	420	4150	828
11	Fac2/2	251	333	NULL
12	Fac2/3	433	1536	NULL
...
20	Fac2/11	13680	30765	4597
21	Fac2/12	10576	NULL	19068
...