title: SQL SERVER PIVOT table with joins and dynamic columns
body: <p>I have a query that I need to pivot and having trouble.</p>
  
  <p>Versions of SQL Server is 2005 and 2008.</p>
  
  <p>The query is derived from common table expression</p>
  
  <pre><code>DECLARE 
  @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX),
  @in_iYearFrom int, @in_iYearTo int,
  @in_iMonthFrom int, @in_iMonthTo int,
  @in_vsPlanID varchar(100)
  @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX),
  @in_iYearFrom int, @in_iYearTo int,
  @in_iMonthFrom int, @in_iMonthTo int,
  @in_vsPlanID varchar(100)    
  
  SELECT 
   @in_iYearFrom = 2012, @in_iYearTo = 2013, @in_iMonthFrom = 11, @in_iMonthTo = 2, @in_vsPlanID = '25,28'
  
  select @cols = STUFF(
              (SELECT DISTINCT
                  ',' + QUOTENAME(Convert(varchar(4),Year(b.run_date)) + ', ' +  DateName(month,b.run_date)) AS run_date
                  FROM tblBill b
                  WHERE b.plan_id IN (SELECT * FROM dbo.fnStringToTable(@in_vsPlanID,','))
                  AND Year(b.run_date) * 100 + MONTH(b.run_date) &gt;= @in_iYearFrom * 100 + @in_iMonthFrom
                  AND Year(b.run_date) * 100 + MONTH(b.run_date) &lt;= @in_iYearTo * 100 + @in_iMonthTo 
                  GROUP BY b.run_date
                  ORDER BY run_date
          FOR XML PATH(''), TYPE
          ).value('.', 'NVARCHAR(MAX)') 
      ,1,1,'')
  
  SET @query = N'
  ;WITH cteBills (total_premium, run_month, run_year, plan_id) AS
  (
   SELECT 
  SUM(Round(ebs.employee_premium,2) + Round(ebs.employer_premium,2) + 
      Round(ebs.ee_tax_prov,2) + Round(ebs.er_tax_prov,2) + 
      Round(ebs.ee_tax_fed,2) + Round(ebs.er_tax_fed,2) + 
      Round(ebs.ee_tax_hst,2) + Round(ebs.er_tax_hst,2)
  ) AS total_premium,
  Month(b.run_date), Year(b.run_date), b.plan_id
  FROM EmpBillStatement ebs 
  INNER JOIN tblBillStatementBenefit bsb ON bsb.billstatementbenefit_id = ebs.billstatementbenefit_id
  INNER JOIN tblBillStatement bs ON bs.billstatement_id = bsb.billstatement_id
  INNER JOIN tblBill b ON b.bill_id = bs.bill_id
  WHERE b.plan_id IN (SELECT * FROM dbo.fnStringToTable(@vsPlanID, '',''))
  AND Year(b.run_date) * 100 + MONTH(b.run_date) &gt;= @iYearFrom * 100 + @iMonthFrom
  AND Year(b.run_date) * 100 + MONTH(b.run_date) &lt;= @iYearTo * 100 + @iMonthTo 
  AND b.confirmed_bill = 1
  GROUP BY b.plan_id, Month(b.run_date), Year(b.run_date)
    ),
   cteBillsAdj (total_adj, run_month, run_year, plan_id) AS 
   (            
   SELECT 
     SUM(
      Round(ISNULL(adjust_cost_er,0),2) + 
      Round(ISNULL(adjust_cost_ee,0),2) +
      Round(ISNULL(adjust_tax_ee_prov,0),2) +
      Round(ISNULL(adjust_tax_er_prov,0),2) +
      Round(ISNULL(adjust_tax_ee_hst,0),2) + 
      Round(ISNULL(adjust_tax_er_hst,0),2) + 
      Round(ISNULL(adjust_tax_ee_fed,0),2) + 
      Round(ISNULL(adjust_tax_er_fed,0),2)
  ) AS total_premium,
  Month(b.run_date), Year(b.run_date), b.plan_id
  FROM tblBillAdjustmentBenefit e
  INNER JOIN tblBillAdjustment ba ON (ba.billadjustment_id = e.billadjustment_id)
  INNER JOIN tblBillStatementBenefit bsb ON bsb.billstatementbenefit_id = e.billstatementbenefit_id
  INNER JOIN tblBillStatement bs ON bs.billstatement_id = bsb.billstatement_id                 
  INNER JOIN tblBill b ON b.bill_id = bs.bill_id
  WHERE b.plan_id IN (SELECT * FROM dbo.fnStringToTable(@vsPlanID, '',''))
  AND Year(b.run_date) * 100 + MONTH(b.run_date) &gt;= @iYearFrom * 100 + @iMonthFrom
  AND Year(b.run_date) * 100 + MONTH(b.run_date) &lt;= @iYearTo * 100 + @iMonthTo 
  AND b.confirmed_bill = 1
  GROUP BY b.plan_id, Month(b.run_date), Year(b.run_date)
  )
  select plan_id, ' + @cols + '
   from
   (
     SELECT 
      b.plan_id, 
      (Convert(varchar(4),b.run_year) + '', '' +  DateName(month,CAST(''1900-'' + Convert(varchar(2),b.run_month) + ''-01'' AS DATETIME))) AS billdate, 
      ISNULL(b.total_premium,0) + ISNULL(a.total_adj,0) AS total
  FROM cteBills b
  LEFT JOIN cteBillsAdj a 
      ON a.run_month = b.run_month 
      AND b.run_year = a.run_year 
      AND b.plan_id = a.plan_id        
  ) d
  pivot
  (
    sum(total)
    for billdate in (' + @cols + ')
  ) piv;
  '
  execute sp_executesql @query, N'@iYearFrom int, @iYearTo int, @iMonthFrom int, @iMonthTo int, @vsPlanID varchar(100)', 
                                  @in_iYearFrom, @in_iYearTo, @in_iMonthFrom, @in_iMonthTo, @in_vsPlanID;
  </code></pre>
  
  <p>Data is displayed like this</p>
  
  <pre><code>plan_id     billdate                             total
  ----------- ------------------------------------ -------------
  25          2012, November                       60117.56000
  25          2012, December                       61515.17000
  25          2013, January                        60791.62000
  25          2013, February                       60745.29000
  28          2012, November                       1564.69000
  28          2012, December                       1564.69000
  28          2013, January                        1564.69000
  28          2013, February                       1590.44000
  </code></pre>
  
  <p>I need it to be in this format</p>
  
  <pre><code>plan_id     2012, November   2012, December   2013, January   2013, February
  -----------------------------------------------------------------------------
  25          60117.56000      61515.17000      60791.62000     60745.29000
  28          1564.69000       1564.69000       1564.69000      1590.44000
  </code></pre>
  
  <p>There can be more plan_id's and more dates to span across.</p>
  
  <p>Thank you in advance</p>