|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Iteration in a stored procedureof the other columns in the same table. If it finds a 'one' in each column, it counts the number of 'paired data' . As there can be up to 60 columns and 60 rows in any table , can anyone suggest a shorter , more efficient way of writing the following code? Any assistanc ewould be appreciated. Thank you. ALTER PROCEDURE GetXXXXXXAll /* ( @IdNumber = 1 ) */ AS Begin /* SET NOCOUNT ON */ SELECT COUNT ([1]) As Expr1, COUNT([1] + [2]) AS Expr2, COUNT([1] + [3]) AS Expr3, COUNT([1] + [4]) AS Expr4, COUNT([1] + [5]) AS Expr5, COUNT([1] + [6]) AS Expr6,COUNT([1] + [7]) AS Expr7, COUNT([1] + [8]) AS Expr8, COUNT([1] + [9]) AS Expr9, COUNT([1] + [10]) AS Expr10, COUNT([1] + [11]) AS Expr11, COUNT([1] + [12]) AS Expr12,COUNT([1] + [13]) AS Expr13, COUNT([1] + [14]) AS Expr14, COUNT([1] + [15]) AS Expr15, COUNT([1] + [16]) AS Expr16,COUNT([1] + [17]) AS Expr17, COUNT([1] + [18]) AS Expr18, COUNT([1] + [19]) AS Expr19, COUNT([1] + [20]) AS Expr20,COUNT([1] + [21]) AS Expr21, COUNT([1] + [22]) AS Expr22, COUNT([1] + [23]) AS Expr23, COUNT([1] + [24]) AS Expr24,COUNT([1] + [25]) AS Expr25, COUNT([1] + [26]) AS Expr26, COUNT([1] + [27]) AS Expr27, COUNT([1] + [28]) AS Expr28,COUNT([1] + [29]) AS Expr29, COUNT([1] + [30]) AS Expr30, COUNT ([2]) As Expr31, COUNT([2] + [3]) AS Expr32, COUNT([2] + [4]) AS Expr33, COUNT([2] + [5]) AS Expr34, COUNT([2] + [6]) AS Expr35,COUNT([2] + [7]) AS Expr36, COUNT([2] + [8]) AS Expr37, COUNT([2] + [9]) AS Expr38, COUNT([2] + [10]) AS Expr39, COUNT([2] + [11]) AS Expr40, COUNT([2] + [12]) AS Expr41,COUNT([2] + [13]) AS Expr42, COUNT([2] + [14]) AS Expr43, COUNT([2] + [15]) AS Expr44, COUNT([2] + [16]) AS Expr45,COUNT([2] + [17]) AS Expr46, COUNT([2] + [18]) AS Expr47, COUNT([2] + [19]) AS Expr48, COUNT([2] + [20]) AS Expr49,COUNT([2] + [21]) AS Expr50, COUNT([2] + [22]) AS Expr51, COUNT([2] + [23]) AS Expr52, COUNT([2] + [24]) AS Expr53,COUNT([2] + [25]) AS Expr54, COUNT([2] + [26]) AS Expr55, COUNT([2] + [27]) AS Expr56, COUNT([2] + [28]) AS Expr57,COUNT([2] + [29]) AS Expr58, COUNT([2] + [30]) AS Expr59, COUNT ([3]) As Expr60, COUNT([3] + [4]) AS Expr61, COUNT([3] + [5]) AS Expr62, COUNT([3] + [6]) AS Expr63,COUNT([3] + [7]) AS Expr64, COUNT([3] + [8]) AS Expr65, COUNT([3] + [9]) AS Expr66, COUNT([3] + [10]) AS Expr67, COUNT([3] + [11]) AS Expr68, COUNT([3] + [12]) AS Expr69,COUNT([3] + [13]) AS Expr70, COUNT([3] + [14]) AS Expr71, COUNT([3] + [15]) AS Expr72, COUNT([3] + [16]) AS Expr73,COUNT([3] + [17]) AS Expr74, COUNT([3] + [18]) AS Expr75, COUNT([3] + [19]) AS Expr76, COUNT([3] + [20]) AS Expr77,COUNT([3] + [21]) AS Expr78, COUNT([3] + [22]) AS Expr79, COUNT([3] + [23]) AS Expr80, COUNT([3] + [24]) AS Expr81,COUNT([3] + [25]) AS Expr82, COUNT([3] + [26]) AS Expr83, COUNT([3] + [27]) AS Expr84, COUNT([3] + [28]) AS Expr85,COUNT([3] + [29]) AS Expr86, COUNT([3] + [30]) AS Expr87, COUNT ([4]) As Expr88, COUNT([4] + [5]) AS Expr89, COUNT([4] + [6]) AS Expr90,COUNT([4] + [7]) AS Expr91, COUNT([4] + [8]) AS Expr92, COUNT([4] + [9]) AS Expr93, COUNT([4] + [10]) AS Expr94, COUNT([4] + [11]) AS Expr95, COUNT([4] + [12]) AS Expr96,COUNT([4] + [13]) AS Expr97, COUNT([4] + [14]) AS Expr98, COUNT([4] + [15]) AS Expr99, COUNT([4] + [16]) AS Expr100,COUNT([4] + [17]) AS Expr101, COUNT([4] + [18]) AS Expr102, COUNT([4] + [19]) AS Expr103, COUNT([4] + [20]) AS Expr104,COUNT([4] + [21]) AS Expr105, COUNT([4] + [22]) AS Expr106, COUNT([4] + [23]) AS Expr107, COUNT([4] + [24]) AS Expr108,COUNT([4] + [25]) AS Expr109, COUNT([4] + [26]) AS Expr110, COUNT([4] + [27]) AS Expr111, COUNT([4] + [28]) AS Expr112,COUNT([4] + [29]) AS Expr113, COUNT([4] + [30]) AS Expr114, COUNT ([5]) As Expr115, COUNT([5] + [6]) AS Expr116,COUNT([5] + [7]) AS Expr117, COUNT([5] + [8]) AS Expr118, COUNT([5] + [9]) AS Expr119, COUNT([5] + [10]) AS Expr120, COUNT([5] + [11]) AS Expr121, COUNT([5] + [12]) AS Expr122,COUNT([5] + [13]) AS Expr123, COUNT([5] + [14]) AS Expr124, COUNT([5] + [15]) AS Expr125, COUNT([5] + [16]) AS Expr126,COUNT([5] + [17]) AS Expr127, COUNT([5] + [18]) AS Expr128, COUNT([5] + [19]) AS Expr129, COUNT([5] + [20]) AS Expr130,COUNT([5] + [21]) AS Expr131, COUNT([5] + [22]) AS Expr132, COUNT([5] + [23]) AS Expr133, COUNT([5] + [24]) AS Expr134,COUNT([5] + [25]) AS Expr135, COUNT([5] + [26]) AS Expr136, COUNT([5] + [27]) AS Expr137, COUNT([5] + [28]) AS Expr138,COUNT([5] + [29]) AS Expr139, COUNT([5] + [30]) AS Expr140 .. .. .. etc .. .. COUNT([60] + [60]) AS Expr1859 FROM XXXXXXFrequencyTable WHERE ([1] = 1) OR ([1] = 1) AND ([2] = 1) OR ([1] = 1) AND ([3] = 1) OR ([1] = 1) AND ([4] = 1) OR ([1] = 1) AND ([5] = 1) OR ([1] = 1) AND ([6] = 1) OR ([1] = 1) AND ([7] = 1) OR ([1] = 1) AND ([8] = 1) OR ([1] = 1) AND ([9] = 1) OR ([1] = 1) AND ([10] = 1) OR ([1] = 1) AND ([11] = 1) OR ([1] = 1) AND ([12] = 1) OR ([1] = 1) AND ([13] = 1) OR ([1] = 1) AND ([14] = 1) OR ([1] = 1) AND ([15] = 1) OR ([1] = 1) AND ([16] = 1) OR ([1] = 1) AND ([17] = 1) OR ([1] = 1) AND ([18] = 1) OR ([1] = 1) AND ([19] = 1) OR ([1] = 1) AND ([20] = 1) OR ([1] = 1) AND ([21] = 1) OR ([1] = 1) AND ([22] = 1) OR ([1] = 1) AND ([23] = 1) OR ([1] = 1) AND ([24] = 1) OR ([1] = 1) AND ([25] = 1) OR ([1] = 1) AND ([26] = 1) OR ([1] = 1) AND ([27] = 1) OR ([1] = 1) AND ([28] = 1) OR ([1] = 1) AND ([29] = 1) OR ([1] = 1) AND ([30] = 1) OR ([2] = 1) OR ([2] = 1) AND ([3] = 1) OR ([2] = 1) AND ([4] = 1) OR ([2] = 1) AND ([5] = 1) OR ([2] = 1) AND ([6] = 1) OR ([2] = 1) AND ([7] = 1) OR ([2] = 1) AND ([8] = 1) OR ([2] = 1) AND ([9] = 1) OR ([2] = 1) AND ([10] = 1) OR ([2] = 1) AND ([11] = 1) OR ([2] = 1) AND ([12] = 1) OR ([2] = 1) AND ([13] = 1) OR ([2] = 1) AND ([14] = 1) OR ([2] = 1) AND ([15] = 1) OR ([2] = 1) AND ([16] = 1) OR ([2] = 1) AND ([17] = 1) OR ([2] = 1) AND ([18] = 1) OR ([2] = 1) AND ([19] = 1) OR ([2] = 1) AND ([20] = 1) OR ([2] = 1) AND ([21] = 1) OR ([2] = 1) AND ([22] = 1) OR ([2] = 1) AND ([23] = 1) OR ([2] = 1) AND ([24] = 1) OR ([2] = 1) AND ([25] = 1) OR ([2] = 1) AND ([26] = 1) OR ([2] = 1) AND ([27] = 1) OR ([2] = 1) AND ([28] = 1) OR ([2] = 1) AND ([29] = 1) OR ([2] = 1) AND ([30] = 1) OR ([3] = 1) OR ([3] = 1) AND ([4] = 1) OR ([3] = 1) AND ([5] = 1) OR ([3] = 1) AND ([6] = 1) OR ([3] = 1) AND ([7] = 1) OR ([3] = 1) AND ([8] = 1) OR ([3] = 1) AND ([9] = 1) OR ([3] = 1) AND ([10] = 1) OR ([3] = 1) AND ([11] = 1) OR ([3] = 1) AND ([12] = 1) OR ([3] = 1) AND ([13] = 1) OR ([3] = 1) AND ([14] = 1) OR ([3] = 1) AND ([15] = 1) OR ([3] = 1) AND ([16] = 1) OR ([3] = 1) AND ([17] = 1) OR ([3] = 1) AND ([18] = 1) OR ([3] = 1) AND ([19] = 1) OR ([3] = 1) AND ([20] = 1) OR ([3] = 1) AND ([21] = 1) OR ([3] = 1) AND ([22] = 1) OR ([3] = 1) AND ([23] = 1) OR ([3] = 1) AND ([24] = 1) OR ([3] = 1) AND ([25] = 1) OR ([3] = 1) AND ([26] = 1) OR ([3] = 1) AND ([27] = 1) OR ([3] = 1) AND ([28] = 1) OR ([3] = 1) AND ([29] = 1) OR ([3] = 1) AND ([30] = 1) OR ([4] = 1) OR ([4] = 1) AND ([5] = 1) OR ([4] = 1) AND ([6] = 1) OR ([4] = 1) AND ([7] = 1) OR ([4] = 1) AND ([8] = 1) OR ([4] = 1) AND ([9] = 1) OR ([4] = 1) AND ([10] = 1) OR ([4] = 1) AND ([11] = 1) OR ([4] = 1) AND ([12] = 1) OR ([4] = 1) AND ([13] = 1) OR ([4] = 1) AND ([14] = 1) OR ([4] = 1) AND ([15] = 1) OR ([4] = 1) AND ([16] = 1) OR ([4] = 1) AND ([17] = 1) OR ([4] = 1) AND ([18] = 1) OR ([4] = 1) AND ([19] = 1) OR ([4] = 1) AND ([20] = 1) OR ([4] = 1) AND ([21] = 1) OR ([4] = 1) AND ([22] = 1) OR ([4] = 1) AND ([23] = 1) OR ([4] = 1) AND ([24] = 1) OR ([4] = 1) AND ([25] = 1) OR ([4] = 1) AND ([26] = 1) OR ([4] = 1) AND ([27] = 1) OR ([4] = 1) AND ([28] = 1) OR ([4] = 1) AND ([29] = 1) OR ([4] = 1) AND ([30] = 1) OR ([5] = 1) OR ([5] = 1) AND ([6] = 1) OR ([5] = 1) AND ([7] = 1) OR ([5] = 1) AND ([8] = 1) OR ([5] = 1) AND ([9] = 1) OR ([5] = 1) AND ([10] = 1) OR ([5] = 1) AND ([11] = 1) OR ([5] = 1) AND ([12] = 1) OR ([5] = 1) AND ([13] = 1) OR ([5] = 1) AND ([14] = 1) OR ([5] = 1) AND ([15] = 1) OR ([5] = 1) AND ([16] = 1) OR ([5] = 1) AND ([17] = 1) OR ([5] = 1) AND ([18] = 1) OR ([5] = 1) AND ([19] = 1) OR ([5] = 1) AND ([20] = 1) OR ([5] = 1) AND ([21] = 1) OR ([5] = 1) AND ([22] = 1) OR ([5] = 1) AND ([23] = 1) OR ([5] = 1) AND ([24] = 1) OR ([5] = 1) AND ([25] = 1) OR ([5] = 1) AND ([26] = 1) OR ([5] = 1) AND ([27] = 1) OR ([5] = 1) AND ([28] = 1) OR ([5] = 1) AND ([29] = 1) OR ([5] = 1) AND ([30] = 1) .. .. .. etc .. ([60] = 1) AND ([60] = 1) END onecorp wrote:
> I have a storde procedure which compares one column of a table against each Holy moley!!! What in the world is this for? If the values of these > of the other columns in the same table. If it finds a 'one' in each column, > it counts the number of 'paired data' . As there can be up to 60 columns and > 60 rows in any table , can anyone suggest a shorter , more efficient way of > writing the following code? Any assistanc ewould be appreciated. Thank you. > fields are truly limited to 1 or 0, you could probably do something like SUM([1] * [2]) AS Expr2 instead of COUNT([1] + [2]) AS Expr2, and eliminate the WHERE clause entirely. If [1] = 1 and [2] = 0, the SUM is going to increase by zero (1 * 0), essentially the same effect you're getting by counting WHERE ([1] = 1 AND [2] = 1). Does that make sense? Hi There,
I hope this help Select 1 R ,1 val1,1 val2,1 val3 , 1 val4 into dummy insert into dummy Select 2 R ,1 val1,1 val2,1 val3 , 1 val4 insert into dummy Select 3 R ,1 val1,1 val2,1 val3 , 4 val4 Select * from dummy Select *, ( case when val1=1 then 1 else 0 end + case when val2=1 then 1 else 0 end + case when val3=1 then 1 else 0 end + case when val4=1 then 1 else 0 end )/2 As Pairs From dummy With Warm regards Jatinder Singh http://jatindersingh.blogspot.com Tracy McKibben wrote: Show quote > onecorp wrote: > > I have a storde procedure which compares one column of a table against each > > of the other columns in the same table. If it finds a 'one' in each column, > > it counts the number of 'paired data' . As there can be up to 60 columns and > > 60 rows in any table , can anyone suggest a shorter , more efficient way of > > writing the following code? Any assistanc ewould be appreciated. Thank you. > > > > Holy moley!!! What in the world is this for? If the values of these > fields are truly limited to 1 or 0, you could probably do something like > SUM([1] * [2]) AS Expr2 instead of COUNT([1] + [2]) AS Expr2, and > eliminate the WHERE clause entirely. If [1] = 1 and [2] = 0, the SUM is > going to increase by zero (1 * 0), essentially the same effect you're > getting by counting WHERE ([1] = 1 AND [2] = 1). > > Does that make sense? Hi,
Thanks for your post! From your description, I understand that: A table of your database includes 60 columns and 60 rows. You wanted to get a 60*60 matrix, in which any item ([x,y]) value represents the count when column x equals column y and both of their values are 1. If I have misunderstood, please feel free to let me know. If the column type is bit, Tracy's idea is wonderful. I also recommend you use SUM expression to retrieve the count value. Otherwise, in this case, the where clause can be written as "WHERE [1]+[2]+[3]+...+[60]>0". However if the column type is not bit and the value may be out of 0 and 1, the where clause should be separated. I recommend you write a function which can count by accepting the two parameters of each coordinates. And then you can get all items count by this way: SELECT proc_selcount(1,1) as [1_1],proc_selcount(1,2) as [1_2],proc_selcount(1,3) as [1_3],....,proc_selcount(60,60) as [60_60] into MATRIX; SELECT * FROM MATRIX; If you have any other concerns, please feel free to let me know. It's my pleasure to be of assistance. +++++++++++++++++++++++++++ Charles Wang Microsoft Online Partner Support +++++++++++++++++++++++++++ When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== Business-Critical Phone Support (BCPS) provides you with technical phone support at no charge during critical LAN outages or "business down" situations. This benefit is available 24 hours a day, 7 days a week to all Microsoft technology partners in the United States and Canada. This and other support options are available here: BCPS: https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469 Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/ If you are outside the United States, please visit our International Support page: http://support.microsoft.com/default.aspx?scid=%2finternational.aspx. ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. Hi,
Can you give the table definition and the insert script for the data? I have got the result you had mailed. Moreover, is it always 60 columns or will it change? Hi,
Since I didn't get the data, I created with a temp table which has 6 columns. It can be extended to 60 columns or how many ever you want, just add those columns in the pivot and unpivot expression. Let me know if this was what you expected. -- Insert script Select 1 [1],0 [2],1 [3] , 1 [4] , 0 [5], 1 [6] into #temp insert into #temp Select 1 ,1 ,0 , 1,0,0 insert into #temp Select 1 ,1 ,1 , 1 ,1,1 insert into #temp Select 1 ,0 ,0 , 1,1,0 insert into #temp Select 1 ,1 ,1 , 0,0,1 insert into #temp Select 0 ,1 ,1 , 0,1,0 --Query with cte as( select row_id, loc, val from (select row_number() over (order by [1]) as row_id, * from #temp) as a unpivot (val for loc in ([1],[2],[3],[4],[5],[6])) as U ), cte1 as( select a.loc as loc,b.loc as loc2,sum(a.val*b.val) as cnt from cte a, cte b where a.row_id = b.row_id and a.loc <= b.loc group by a.loc,b.loc ) select * from cte1 pivot ( sum(cnt) for loc2 in ([1],[2],[3],[4],[5],[6])) as P Hope this helps. -Omni Thank you to all for their assistance.
To Omnibuzz ......my apologies for the lack of a reply, but it was probably dut to time differences etc. I originally derived the table by using in-line SQL. I was then advised that that was not a good idea, and hence came up with the stored procedure (one example of my attempts being the one I posted ). Regarding the table definition: At the first block of code referring to NUmber [1] I used create table. At each block thereafter, I used 'insert into table' thereby building up each row. To display the results, I used a gridview control to obtain the data from the tbale created in the stored procedure and a 'sort' to get the rows in the right order. In your reply you wrote: -- Insert script Select 1 [1],0 [2],1 [3] , 1 [4] , 0 [5], 1 [6] into #temp insert into #temp Select 1 ,1 ,0 , 1,0,0 insert into #temp Select 1 ,1 ,1 , 1 ,1,1 insert into #temp Select 1 ,0 ,0 , 1,1,0 insert into #temp Select 1 ,1 ,1 , 0,0,1 insert into #temp Select 0 ,1 ,1 , 0,1,0 Why in the select stmt is it 1 [1],0 [2],1 [3] , 1 [4] , 0 [5], 1 [6] and not 1 [1],1 [2],1 [3] , 1 [4] , 1 [5], 1 [6] ? What do the 'ones' and 'zeroes' mean in this exp ? insert into #temp Select 1 ,1 ,0 , 1,0,0 Dear Mr WANG, Can you please tell me what proc_selcount means? Thank you Show quote "onecorp" wrote: > I have a stored procedure which compares one column of a table against each > of the other columns in the same table. If it finds a 'one' in each column, > it counts the number of 'paired data' . As there can be up to 60 columns and > 60 rows in any table , can anyone suggest a shorter , more efficient way of > writing the following code? Any assistance would be appreciated. Thank you. > > ALTER PROCEDURE GetXXXXXXAll > /* > ( > @IdNumber = 1 > ) > */ > AS > > Begin > > /* SET NOCOUNT ON */ > > SELECT COUNT ([1]) As Expr1, > COUNT([1] + [2]) AS Expr2, COUNT([1] + [3]) AS Expr3, COUNT([1] > + [4]) AS Expr4, COUNT([1] + [5]) AS Expr5, > COUNT([1] + [6]) AS Expr6,COUNT([1] + [7]) AS Expr7, COUNT([1] + > [8]) AS Expr8, COUNT([1] + [9]) AS Expr9, > COUNT([1] + [10]) AS Expr10, COUNT([1] + [11]) AS Expr11, > COUNT([1] + [12]) AS Expr12,COUNT([1] + [13]) AS Expr13, > COUNT([1] + [14]) AS Expr14, COUNT([1] + [15]) AS Expr15, > COUNT([1] + [16]) AS Expr16,COUNT([1] + [17]) AS Expr17, > COUNT([1] + [18]) AS Expr18, COUNT([1] + [19]) AS Expr19, > COUNT([1] + [20]) AS Expr20,COUNT([1] + [21]) AS Expr21, > COUNT([1] + [22]) AS Expr22, COUNT([1] + [23]) AS Expr23, > COUNT([1] + [24]) AS Expr24,COUNT([1] + [25]) AS Expr25, > COUNT([1] + [26]) AS Expr26, COUNT([1] + [27]) AS Expr27, > COUNT([1] + [28]) AS Expr28,COUNT([1] + [29]) AS Expr29, > COUNT([1] + [30]) AS Expr30, > COUNT ([2]) As Expr31, > COUNT([2] + [3]) AS Expr32, COUNT([2] + [4]) AS Expr33, > COUNT([2] + [5]) AS Expr34, > COUNT([2] + [6]) AS Expr35,COUNT([2] + [7]) AS Expr36, COUNT([2] > + [8]) AS Expr37, COUNT([2] + [9]) AS Expr38, > COUNT([2] + [10]) AS Expr39, COUNT([2] + [11]) AS Expr40, > COUNT([2] + [12]) AS Expr41,COUNT([2] + [13]) AS Expr42, > COUNT([2] + [14]) AS Expr43, COUNT([2] + [15]) AS Expr44, > COUNT([2] + [16]) AS Expr45,COUNT([2] + [17]) AS Expr46, > COUNT([2] + [18]) AS Expr47, COUNT([2] + [19]) AS Expr48, > COUNT([2] + [20]) AS Expr49,COUNT([2] + [21]) AS Expr50, > COUNT([2] + [22]) AS Expr51, COUNT([2] + [23]) AS Expr52, > COUNT([2] + [24]) AS Expr53,COUNT([2] + [25]) AS Expr54, > COUNT([2] + [26]) AS Expr55, COUNT([2] + [27]) AS Expr56, > COUNT([2] + [28]) AS Expr57,COUNT([2] + [29]) AS Expr58, > COUNT([2] + [30]) AS Expr59, > COUNT ([3]) As Expr60, > COUNT([3] + [4]) AS Expr61, COUNT([3] + [5]) AS Expr62, > COUNT([3] + [6]) AS Expr63,COUNT([3] + [7]) AS Expr64, COUNT([3] > + [8]) AS Expr65, COUNT([3] + [9]) AS Expr66, > COUNT([3] + [10]) AS Expr67, COUNT([3] + [11]) AS Expr68, > COUNT([3] + [12]) AS Expr69,COUNT([3] + [13]) AS Expr70, > COUNT([3] + [14]) AS Expr71, COUNT([3] + [15]) AS Expr72, > COUNT([3] + [16]) AS Expr73,COUNT([3] + [17]) AS Expr74, > COUNT([3] + [18]) AS Expr75, COUNT([3] + [19]) AS Expr76, > COUNT([3] + [20]) AS Expr77,COUNT([3] + [21]) AS Expr78, > COUNT([3] + [22]) AS Expr79, COUNT([3] + [23]) AS Expr80, > COUNT([3] + [24]) AS Expr81,COUNT([3] + [25]) AS Expr82, > COUNT([3] + [26]) AS Expr83, COUNT([3] + [27]) AS Expr84, > COUNT([3] + [28]) AS Expr85,COUNT([3] + [29]) AS Expr86, > COUNT([3] + [30]) AS Expr87, > COUNT ([4]) As Expr88, > COUNT([4] + [5]) AS Expr89, > COUNT([4] + [6]) AS Expr90,COUNT([4] + [7]) AS Expr91, COUNT([4] > + [8]) AS Expr92, COUNT([4] + [9]) AS Expr93, > COUNT([4] + [10]) AS Expr94, COUNT([4] + [11]) AS Expr95, > COUNT([4] + [12]) AS Expr96,COUNT([4] + [13]) AS Expr97, > COUNT([4] + [14]) AS Expr98, COUNT([4] + [15]) AS Expr99, > COUNT([4] + [16]) AS Expr100,COUNT([4] + [17]) AS Expr101, > COUNT([4] + [18]) AS Expr102, COUNT([4] + [19]) AS Expr103, > COUNT([4] + [20]) AS Expr104,COUNT([4] + [21]) AS Expr105, > COUNT([4] + [22]) AS Expr106, COUNT([4] + [23]) AS Expr107, > COUNT([4] + [24]) AS Expr108,COUNT([4] + [25]) AS Expr109, > COUNT([4] + [26]) AS Expr110, COUNT([4] + [27]) AS Expr111, > COUNT([4] + [28]) AS Expr112,COUNT([4] + [29]) AS Expr113, > COUNT([4] + [30]) AS Expr114, > COUNT ([5]) As Expr115, > COUNT([5] + [6]) AS Expr116,COUNT([5] + [7]) AS Expr117, > COUNT([5] + [8]) AS Expr118, COUNT([5] + [9]) AS Expr119, > COUNT([5] + [10]) AS Expr120, COUNT([5] + [11]) AS Expr121, > COUNT([5] + [12]) AS Expr122,COUNT([5] + [13]) AS Expr123, > COUNT([5] + [14]) AS Expr124, COUNT([5] + [15]) AS Expr125, > COUNT([5] + [16]) AS Expr126,COUNT([5] + [17]) AS Expr127, > COUNT([5] + [18]) AS Expr128, COUNT([5] + [19]) AS Expr129, > COUNT([5] + [20]) AS Expr130,COUNT([5] + [21]) AS Expr131, > COUNT([5] + [22]) AS Expr132, COUNT([5] + [23]) AS Expr133, > COUNT([5] + [24]) AS Expr134,COUNT([5] + [25]) AS Expr135, > COUNT([5] + [26]) AS Expr136, COUNT([5] + [27]) AS Expr137, > COUNT([5] + [28]) AS Expr138,COUNT([5] + [29]) AS Expr139, > COUNT([5] + [30]) AS Expr140 > . > . > . etc > . > . > COUNT([60] + [60]) AS Expr1859 > > > > FROM XXXXXXFrequencyTable > WHERE ([1] = 1) OR > ([1] = 1) AND ([2] = 1) OR > ([1] = 1) AND ([3] = 1) OR > ([1] = 1) AND ([4] = 1) OR > ([1] = 1) AND ([5] = 1) OR > ([1] = 1) AND ([6] = 1) OR > ([1] = 1) AND ([7] = 1) OR > ([1] = 1) AND ([8] = 1) OR > ([1] = 1) AND ([9] = 1) OR > ([1] = 1) AND ([10] = 1) OR > ([1] = 1) AND ([11] = 1) OR > ([1] = 1) AND ([12] = 1) OR > ([1] = 1) AND ([13] = 1) OR > ([1] = 1) AND ([14] = 1) OR > ([1] = 1) AND ([15] = 1) OR > ([1] = 1) AND ([16] = 1) OR > ([1] = 1) AND ([17] = 1) OR > ([1] = 1) AND ([18] = 1) OR > ([1] = 1) AND ([19] = 1) OR > ([1] = 1) AND ([20] = 1) OR > ([1] = 1) AND ([21] = 1) OR > ([1] = 1) AND ([22] = 1) OR > ([1] = 1) AND ([23] = 1) OR > ([1] = 1) AND ([24] = 1) OR > ([1] = 1) AND ([25] = 1) OR > ([1] = 1) AND ([26] = 1) OR > ([1] = 1) AND ([27] = 1) OR > ([1] = 1) AND ([28] = 1) OR > ([1] = 1) AND ([29] = 1) OR > ([1] = 1) AND ([30] = 1) OR > ([2] = 1) OR > ([2] = 1) AND ([3] = 1) OR > ([2] = 1) AND ([4] = 1) OR > ([2] = 1) AND ([5] = 1) OR > ([2] = 1) AND ([6] = 1) OR > ([2] = 1) AND ([7] = 1) OR > ([2] = 1) AND ([8] = 1) OR > ([2] = 1) AND ([9] = 1) OR > ([2] = 1) AND ([10] = 1) OR > ([2] = 1) AND ([11] = 1) OR > ([2] = 1) AND ([12] = 1) OR > ([2] = 1) AND ([13] = 1) OR > ([2] = 1) AND ([14] = 1) OR > ([2] = 1) AND ([15] = 1) OR > ([2] = 1) AND ([16] = 1) OR > ([2] = 1) AND ([17] = 1) OR > ([2] = 1) AND ([18] = 1) OR > ([2] = 1) AND ([19] = 1) OR > ([2] = 1) AND ([20] = 1) OR > ([2] = 1) AND ([21] = 1) OR > ([2] = 1) AND ([22] = 1) OR > ([2] = 1) AND ([23] = 1) OR > ([2] = 1) AND ([24] = 1) OR > ([2] = 1) AND ([25] = 1) OR > ([2] = 1) AND ([26] = 1) OR > ([2] = 1) AND ([27] = 1) OR > ([2] = 1) AND ([28] = 1) OR > ([2] = 1) AND ([29] = 1) OR > ([2] = 1) AND ([30] = 1) OR > ([3] = 1) OR > ([3] = 1) AND ([4] = 1) OR > ([3] = 1) AND ([5] = 1) OR > ([3] = 1) AND ([6] = 1) OR > ([3] = 1) AND ([7] = 1) OR > ([3] = 1) AND ([8] = 1) OR > ([3] = 1) AND ([9] = 1) OR > ([3] = 1) AND ([10] = 1) OR > ([3] = 1) AND ([11] = 1) OR > ([3] = 1) AND ([12] = 1) OR > ([3] = 1) AND ([13] = 1) OR > ([3] = 1) AND ([14] = 1) OR > ([3] = 1) AND ([15] = 1) OR > ([3] = 1) AND ([16] = 1) OR > ([3] = 1) AND ([17] = 1) OR > ([3] = 1) AND ([18] = 1) OR > ([3] = 1) AND ([19] = 1) OR > ([3] = 1) AND ([20] = 1) OR > ([3] = 1) AND ([21] = 1) OR > ([3] = 1) AND ([22] = 1) OR > ([3] = 1) AND ([23] = 1) OR > ([3] = 1) AND ([24] = 1) OR > ([3] = 1) AND ([25] = 1) OR > ([3] = 1) AND ([26] = 1) OR > ([3] = 1) AND ([27] = 1) OR > ([3] = 1) AND ([28] = 1) OR > ([3] = 1) AND ([29] = 1) OR > ([3] = 1) AND ([30] = 1) OR > ([4] = 1) OR > ([4] = 1) AND ([5] = 1) OR > ([4] = 1) AND ([6] = 1) OR > ([4] = 1) AND ([7] = 1) OR > ([4] = 1) AND ([8] = 1) OR > ([4] = 1) AND ([9] = 1) OR > ([4] = 1) AND ([10] = 1) OR > ([4] = 1) AND ([11] = 1) OR > ([4] = 1) AND ([12] = 1) OR > ([4] = 1) AND ([13] = 1) OR > ([4] = 1) AND ([14] = 1) OR > ([4] = 1) AND ([15] = 1) OR > ([4] = 1) AND ([16] = 1) OR > ([4] = 1) AND ([17] = 1) OR > ([4] = 1) AND ([18] = 1) OR > ([4] = 1) AND ([19] = 1) OR > ([4] = 1) AND ([20] = 1) OR > ([4] = 1) AND ([21] = 1) OR > ([4] = 1) AND ([22] = 1) OR > ([4] = 1) AND ([23] = 1) OR > ([4] = 1) AND ([24] = 1) OR > ([4] = 1) AND ([25] = 1) OR > ([4] = 1) AND ([26] = 1) OR > ([4] = 1) AND ([27] = 1) OR > ([4] = 1) AND ([28] = 1) OR > ([4] = 1) AND ([29] = 1) OR > ([4] = 1) AND ([30] = 1) OR > ([5] = 1) OR > ([5] = 1) AND ([6] = 1) OR > ([5] = 1) AND ([7] = 1) OR > ([5] = 1) AND ([8] = 1) OR > ([5] = 1) AND ([9] = 1) OR > ([5] = 1) AND ([10] = 1) OR > ([5] = 1) AND ([11] = 1) OR > ([5] = 1) AND ([12] = 1) OR > ([5] = 1) AND ([13] = 1) OR > ([5] = 1) AND ([14] = 1) OR > ([5] = 1) AND ([15] = 1) OR > ([5] = 1) AND ([16] = 1) OR > ([5] = 1) AND ([17] = 1) OR > ([5] = 1) AND ([18] = 1) OR > ([5] = 1) AND ([19] = 1) OR > ([5] = 1) AND ([20] = 1) OR > ([5] = 1) AND ([21] = 1) OR > ([5] = 1) AND ([22] = 1) OR > ([5] = 1) AND ([23] = 1) OR > ([5] = 1) AND ([24] = 1) OR > ([5] = 1) AND ([25] = 1) OR > ([5] = 1) AND ([26] = 1) OR > ([5] = 1) AND ([27] = 1) OR > ([5] = 1) AND ([28] = 1) OR > ([5] = 1) AND ([29] = 1) OR > ([5] = 1) AND ([30] = 1) > . > . > . etc > . > ([60] = 1) AND ([60] = 1) > END The way I understood...
The values in the columns 1 through 6 can have 0s or 1s. And you want to find the count of all possible pair of columns where both have the value 1. Its just a sample data. You can have it as all 1s or all 0s. Did you run the code and check? Hi ,
I am interested in this issue. Would you mind letting me know the result of the suggestions? If you need further assistance, feel free to let me know. I will be more than happy to be of assistance. Have a great day! +++++++++++++++++++++++++++ Charles Wang Microsoft Online Partner Support +++++++++++++++++++++++++++ When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. Hi,
Thanks for your response. The proc_selcount is a custom function that can count any one or two columns equaling 1. However this name is not canonical, you can specify the name according to your standard. +++++++++++++++++++++++++++ Charles Wang Microsoft Online Partner Support +++++++++++++++++++++++++++ When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. normalization.
you need a new paradigm to store your data. you will fight and fight and fight this data model forever. bite the bullet, and get some help in storing your data in an easier way. Doug wrote:
> normalization. Hi,> > you need a new paradigm to store your data. > > you will fight and fight and fight this data model forever. bite the > bullet, and get some help in storing your data in an easier way. Are you aware of the Usenet etiquette of quoting at least some of the post you're replying to? It's very useful, especially if you're responding to a post that is several days old. Chris Perhaps as an alternative, I could use a DAL / create a table adapter
/ and use code along the lines of that detailed below. (Unfortunately, I can't get it to work yet as it has a type cast error in the line: num3 = "SUM (columnA + columnB) As Expr" The dataset contains columns : Id , [1] , [2], [3] .....[30] For the purposes of the iteration, I do not need the Id column. The other columns are of data type smallint and contain either 1 or null. Is someone able to point me in the right direction? Thank you) ???? Imports MyDataSet Imports System.Data.SqlClient Imports System.Data.Sql Partial Class Default5 Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim pairsAdapter As New MWFNTableAdapters.AMFNFTableAdapter Dim pairs As MWFN.AMFNFDataTable pairs = pairsAdapter.GetData Dim myColumns As Data.DataColumnCollection = pairs.Columns Dim columnA As Data.DataColumn Dim columnB As Data.DataColumn Dim num3 As Integer For Each columnA In myColumns If columnA IsNot "Id" Then For Each columnB In myColumns If columnB IsNot "Id" Then num3 = "SUM (columnA + columnB) As Expr" End If Next End If Next Dim table1()() As Integer = New Integer(29)() {} Dim myColumnHeaders As Integer For myColumnHeaders = 0 To 29 table1(myColumnHeaders) = New Integer(num3 + 1) {} Next myColumnHeaders End Sub Any suugestions would be appreciated. Onecorp Show quote "Chris Lim" wrote: > Doug wrote: > > normalization. > > > > you need a new paradigm to store your data. > > > > you will fight and fight and fight this data model forever. bite the > > bullet, and get some help in storing your data in an easier way. > > Hi, > > Are you aware of the Usenet etiquette of quoting at least some of the > post you're replying to? It's very useful, especially if you're > responding to a post that is several days old. > > Chris > >  |
|||||||||||||||||||||||
Other interesting topics