Tabella PIVOT SQL SERVER con join e colonne dinamiche

Ho una query che ho bisogno di ruotare e avere problemi.

Le versioni di SQL Server sono 2005 e 2008.

La query è derivata dall’espressione della tabella comune

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) = @iYearFrom * 100 + @iMonthFrom 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; 

I dati sono visualizzati in questo modo

 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 

Ho bisogno che sia in questo formato

 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 

Ci possono essere più plan_id e più date da attraversare.

Grazie in anticipo

Dal momento che si desidera trasformare i dati da righe in colonne, allora si vorrà utilizzare la funzione PIVOT . Se si dispone di un numero limitato o di valori noti, è ansible codificare la query con hard-code:

 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; 

Ma se hai un numero sconosciuto di valori, dovrai implementare l’SQL dinamico:

 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;