PIVOT giving incorrect output when no of columns increase

Question

I have a table channel_merge like

Channel_1 | Channel_2
----------------------
COLUMN1   | COLUMN343
COLUMN1   | COLUMN392
COLUMN1   | COLUMN267
COLUMN1   | COLUMN198
COLUMN1   | COLUMN400
COLUMN2   | COLUMN348
COLUMN2   | COLUMN97
COLUMN1   | COLUMN97
COLUMN3   | COLUMN343
COLUMN3   | COLUMN65
COLUMN4   | COLUMN33

where columnX values ranges between column1 to column512 No of rows can vary between 8k to 20k And I wanted a matrix (512X512) with value representing the number of entries between respective combination of columns in matrix.

I broke the 512 int groups of 100, process with PIVOT and later append into full matrix The pivot finction used is as following pivot query

select [COLUMN1],[COLUMN10],[COLUMN100],[COLUMN101],[COLUMN102],[COLUMN103],[COLUMN104],[COLUMN105],[COLUMN106],[COLUMN107],[COLUMN108],[COLUMN109],[COLUMN11],[COLUMN110],[COLUMN111],[COLUMN112],[COLUMN113],[COLUMN114]..100 columns INTO FINAL1
from 
(
select channel_1,channel_1 as CHANNEL_11,CHANNEL_2 from CHANNEL_MERGE 
) P
pivot 
(
count(CHANNEL_11) for CHANNEL_2 in ([COLUMN1],[COLUMN10],[COLUMN100],[COLUMN101],[COLUMN102],[COLUMN103],[COLUMN104],[COLUMN105],[COLUMN106],[COLUMN107],[COLUMN108],[COLUMN109],[COLUMN11],[COLUMN110],[COLUMN111],[COLUMN112],[COLUMN113],[COLUMN114]....100columns)
) as pvt

Which worked fine in one DB

But recently using it other DB. It returns incorrect results and different results when ran different times with my procedure(containing the above query)

When i reduced the grouping from 100 columns to 30 columns the results look proper.

Could you please help me in determining which DB parameter(s) are effecting or can effect the result of above query.

Any limitation of Pivot i need to take in to consideration

Both the Db are of same version SQL server 2008 SP2.

Thanks in advance.

Example: INput Channel_merge table (column1 to column5)considering 5X5

channel_1   channel_2
column1 column2
column2 column1
column2 column3
column2 column3
column2 column3
column2 column4
column3 column2
column3 column2
column3 column2
column3 column5
column3 column5
column4 column2
column5 column3
column5 column3

So the expected output is matrix 5X5

 0  1   0   0   0
 1  0   3   1   0
 0  3   0   0   2
 0  1   0   0   0
 0  0   2   0   0

Answer 1

TADA.. This one was tricky, but had fun doing it..

declare @distcols as nvarchar(max) = ''
select @distcols=@distcols +'['+ (a.channel_2)+'],' from (select distinct channel_2 from TableName) a
set @distcols = substring(@distcols,0,len(@distcols))
declare @dynamicsql as nvarchar(max) = 'select * from
(   
        select channel_1, channel_2, count(channel_1) as distinctcount from TableName 
        group by channel_1, channel_2

) as sourcetable
pivot
(
    Min(distinctcount)
    FOR channel_2 IN ('

set @dynamicsql = @dynamicsql + @distcols + ')
)
as pivottable'

print @dynamicsql
exec (@dynamicsql)

For your reference :