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

score 13 · Accepted Answer · answered Aug 19 '13 at 15:45

13

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;

answered Aug 19 '13 at 15:45

Taryn

242,637
56
362
405

Thanks bluefeet. All works with the exception of assignment of @cols variable. The challenge is that there is no valid object cteBills. It is part of common table expression. As a result I can not assign cols. – user1706426 Aug 19 '13 at 16:36
@user1706426 You should still be able to get the list of columns you will just use the name of the table that you use to create the billdate in your version. – Taryn Aug 19 '13 at 16:40
Got the query working as you suggested. But, now I have another problem. Columns are displayed in this order [2012, December],[2012, November],[2013, February],[2013, January]. ORDER BY treats run_date as string and I can not even cast it in the order by statement. I changed the variable cols assignment in your response. – user1706426 Aug 19 '13 at 17:10
@user1706426 Please edit your post with the exact tables that you are using and don't make edits to the answer. If you need the code to be altered then you should post the correct details in your question. – Taryn Aug 19 '13 at 17:15
Sorry, I changed my query with full details, its just a bit long and more difficult to understand. – user1706426 Aug 19 '13 at 17:44
1

@user1706426 What is the datatype of `run_date`? – Taryn Aug 19 '13 at 17:45
Actual tblBill.run_date is datetime but in the query its varchar since its Convert(varchar(4),Year(b.run_date)) + ', ' + DateName(month,b.run_date)) AS run_date. That is the way I need it to display (2013, November). I know I shouldnt use AS run_date for readability sake. – user1706426 Aug 19 '13 at 17:55
@user1706426 Look at using `row_number()` to help get the correct order -- see this demo -- http://sqlfiddle.com/#!3/0d97a/15 – Taryn Aug 19 '13 at 18:10
You brilliant answer really helped. However, in my case I have a huge FROM and WHERE clauses identical for both queries, I was wondering if there is any elegant way to have to edit these blocks in one place so that the changes are taken into account by both queris (mainly the WHERE caluse is edited a lot by adding removing combining conditions) ? – Enissay Apr 22 '17 at 11:32

SQL SERVER PIVOT table with joins and dynamic columns

1 Answers1

Linked