Problem in dynamic pivoting + sql server 2005

Question

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

Andomar · Accepted Answer · 2009-10-25 10:39:21Z

2

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).

answered Oct 25, 2009 at 10:39

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gbn · Accepted Answer · 2009-10-25 10:46:10Z

2

Row by row, you can't change the number of output columns.

If I understand correctly, you'll have to build it for the maximum number of columns and give NULL/0 for unused columns.

Edit: which is what Andomar is doing but he dynamically chooses the maximum number of columns

edited Oct 25, 2009 at 10:46

answered Oct 25, 2009 at 10:35

gbn

434k84 gold badges602 silver badges690 bronze badges

Collectives™ on Stack Overflow

Problem in dynamic pivoting + sql server 2005

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related