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.
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)
ENDonecorp wrote:
> I have a storde procedure which compares one column of a table against eac
h
> 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 a
nd
> 60 rows in any table , can anyone suggest a shorter , more efficient way o
f
> 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 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:
> onecorp wrote:
> 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 w
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/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.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?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||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 th
e
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
"onecorp" wrote:
> I have a stored procedure which compares one column of a table against eac
h
> 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 a
nd
> 60 rows in any table , can anyone suggest a shorter , more efficient way o
f
> 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 0
s.
Did you run the code and check?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||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.
