具有联接和动态列的SQL SERVER PIVOT表 [英] SQL SERVER PIVOT table with joins and dynamic columns
本文介绍了具有联接和动态列的SQL SERVER PIVOT表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个需要解决的麻烦查询.
I have a query that I need to pivot and having trouble.
SQL Server的版本是2005和2008.
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;
数据显示如下
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
我需要采用这种格式
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
可能有更多的plan_id和更多的日期可以使用.
There can be more plan_id's and more dates to span across.
提前谢谢
推荐答案
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;
但是，如果您拥有未知数量的值，那么您将需要实现动态SQL:
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表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
