Ich habe eine Frage, die ich schwenken und Probleme haben muss.

Versionen von SQL Server sind 2005 und 2008.

Die Abfrage wird vom allgemeinen Tabellenausdruck abgeleitet

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) >= @in_iYearFrom * 100 + @in_iMonthFrom
                AND Year(b.run_date) * 100 + MONTH(b.run_date) <= @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) >= @iYearFrom * 100 + @iMonthFrom
AND Year(b.run_date) * 100 + MONTH(b.run_date) <= @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) >= @iYearFrom * 100 + @iMonthFrom
AND Year(b.run_date) * 100 + MONTH(b.run_date) <= @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;

Die Daten werden so angezeigt

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

Ich brauche es in diesem Format

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

Es können mehr plan_ids und mehr Daten vorhanden sein.

Danke im Voraus