SQL SERVER PIVOT table with joins and dynamic columns

Question

I have a query that I need to pivot and having trouble.

Versions of SQL Server is 2005 and 2008.

The query is derived from common table expression

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;

Data is displayed like this

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

I need it to be in this 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

There can be more plan_id's and more dates to span across.

Thank you in advance

Answer 1

Since you want to transform data from rows into columns, then you will want to use the PIVOT function. If you have a limited number or known values, then you can hard-code the query:

select plan_id, [2012, November], [2012, December], [2013, January], [2013, February]
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 ([2012, November], [2012, December], [2013, January], [2013, February])
) piv;

But if you have an unknown number of values, then you will need to implement dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT  ',' + QUOTENAME(Convert(varchar(4),b.run_year) + ', ' +  DateName(month,CAST('1900-' + Convert(varchar(2),b.run_month) + '-01' AS DATETIME))) ) 
                    from cteBills
                    group by b.run_year, b.run_month
                    order by b.run_year, b.run_month
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = '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
            ) x
            pivot 
            (
                sum(total)
                for billdate in (' + @cols + ')
            ) p '

execute sp_executesql @query;