All geek questions in one place

SQL SERVER PIVOT table with joins and dynamic columns

I have a request that I need to install and have problems.

SQL Server versions 2005 and 2008.

The query is derived from a 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 as follows

 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 may be more plan_id and more dates to cover.

Thank you in advance

+4
source share
1 answer

Since you want to convert data from rows to columns, you will need to use the PIVOT function. If you have a limited number or known values, you can program 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, 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;
+13
source

Source: https://habr.com/ru/post/1497831/

More articles:


All Articles