动态数据透视+ SQL Server 2005中的问题 [英] Problem in dynamic pivoting + sql server 2005
问题描述
我有问题.实际上,在我们的应用程序中,较早的客户被允许每月支付3安装费,但现在可以为任意数量.所以我有较早的查询
declare @tbl table([MonthName] varchar(50), [Installment] int)
insert into @tbl select 'Jan',100 union all
select 'Jan',200 union all select 'Jan',300 union all
select 'Feb',100 union all
select 'Feb',200 union all select 'Feb',300
select [MonthName]
,[100] as '1st installment'
,[200] as '2nd installment'
,[300] as '3rd installment'
from
(select [MonthName],[Installment] from @tbl)as x
pivot
(max([Installment]) for [Installment] in
([100],[200],[300]))as pvt
输出是这个
MonthName 1st installment 2nd installment 3rd installment
Feb 100 200 300
Jan 100 200 300
但是正如我说的那样,现在分期付款可能会有所不同(例如,在1个月内可以是4,而在下个月可以是5或3或6),那么在这种情况下如何使动态列旋转? /p>
预先感谢
您可以动态构建查询:
declare @installment_list varchar(max)
select @installment_list = IsNull(@installment_list,'') +
'[' + cast(Installment as varchar(32)) + '],'
from #tbl
group by Installment
-- Remove last comma
set @installment_list = left(@installment_list,len(@installment_list)-1)
declare @dynquery varchar(max)
set @dynquery = 'select * ' +
'from #tbl ' +
'pivot ( ' +
' max([Installment]) ' +
' for [Installment] ' +
' in (' + @installment_list + ') ' +
') as pvt'
exec (@dynquery)
请注意,表变量在exec()内部不可见,因此我已更改为临时变量(#tbl而不是@tbl).
I have a problem. Actually in our application, earlier the customer was allowed to pay 3 installemnt per month but now it can be any number . So I have the earlier query
declare @tbl table([MonthName] varchar(50), [Installment] int)
insert into @tbl select 'Jan',100 union all
select 'Jan',200 union all select 'Jan',300 union all
select 'Feb',100 union all
select 'Feb',200 union all select 'Feb',300
select [MonthName]
,[100] as '1st installment'
,[200] as '2nd installment'
,[300] as '3rd installment'
from
(select [MonthName],[Installment] from @tbl)as x
pivot
(max([Installment]) for [Installment] in
([100],[200],[300]))as pvt
The output is this
MonthName 1st installment 2nd installment 3rd installment
Feb 100 200 300
Jan 100 200 300
But as I say that the installments can vary now ( say in 1 month it can be 4 while in next month it can be 5 or 3 or 6), so how can I make a dynamic column pivoting in this case?
Thanks in advance
You can build the query dynamically:
declare @installment_list varchar(max)
select @installment_list = IsNull(@installment_list,'') +
'[' + cast(Installment as varchar(32)) + '],'
from #tbl
group by Installment
-- Remove last comma
set @installment_list = left(@installment_list,len(@installment_list)-1)
declare @dynquery varchar(max)
set @dynquery = 'select * ' +
'from #tbl ' +
'pivot ( ' +
' max([Installment]) ' +
' for [Installment] ' +
' in (' + @installment_list + ') ' +
') as pvt'
exec (@dynquery)
Note that table variables are not visible inside the exec(), so I've changed to a temporary variable (#tbl instead of @tbl).
这篇关于动态数据透视+ SQL Server 2005中的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!