SQL SERVER PIVOT table with joins and dynamic columns
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;