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…

Accepted Answer

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;
```

SQL SERVER PIVOT table with joins and dynamic columns

Solution

Problem