The problem in dynamic rotation + sql 2005 server

Question

I have a problem. In fact, in our application, previously the customer was allowed to pay 3 instammnt per month, but now it can be any number. Therefore, I have an earlier request

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 

This is displayed

 MonthName 1st installment 2nd installment 3rd installment Feb 100 200 300 Jan 100 200 300 

But, as I said, contributions can change now (say, after 1 month it can be 4, and next month it can be 5 or 3 or 6), so how can I make a dynamic column in this case?

Thanks in advance

Answer 1

You can build a 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 displayed inside exec (), so I changed the temporary variable (#tbl instead of @tbl).

Answer 2

Row by row, you cannot change the number of output columns.

If I understand correctly, you will need to build it for the maximum number of columns and specify NULL / 0 for unused columns.

Edit: this is what Andomar does, but it dynamically selects the maximum number of columns