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