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
TADA.. This one was tricky, but had fun doing it..
For your reference :