Iterating through SQL Server data

microsoft.public.sqlserver.programming

31 Aug 2006 6:29 AM

onecorp

onecorp 6/28/2006 5:57 PM PST

Question

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

Was this post helpful to you?
   Reply      Top

   Tracy McKibben 6/28/2006 6:26 PM PST

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?

Was this post helpful to you?
   Reply      Top

   jsfromynr 6/29/2006 1:09 AM PST

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:

Click to show or hide original message or reply text.

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?

Was this post helpful to you?
   Reply      Top

   Charles Wang[MSFT] 6/29/2006 1:39 AM PST

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.

Was this post helpful to you?
   Reply      Top

   Omnibuzz 6/29/2006 3:48 AM PST

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/

Was this post helpful to you?
   Reply      Top

   Omnibuzz 6/29/2006 4:18 AM PST

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

Was this post helpful to you?
   Reply      Top

   onecorp 6/30/2006 11:04 PM PST

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

"onecorp" wrote:

Click to show or hide original message or reply text.

Show quote

> 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

Was this post helpful to you?
   Reply      Top

   Omnibuzz 6/30/2006 11:27 PM PST

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?

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/

Was this post helpful to you?
   Reply      Top

   Charles Wang[MSFT] 7/12/2006 3:30 AM PST

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

   Charles Wang[MSFT] 7/2/2006 7:43 PM PST

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.

Was this post helpful to you?
   Reply      Top

    onecorp 8/30/2006 6:14 PM PST

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

"Chris Lim" wrote:

Click to show or hide original message or reply text.

31 Aug 2006 2:48 PM

Charles Wang[MSFT]

Hi Onecorp,
Nice to meet you again.

This time you bring me a long post. :-)
If I don't misunderstand your issue, you want to know how to solve the
problem of SUM(columnA+columnB).

The error should be caused by NULL value in the columns. You can use ISNULL
to get rid of its influence, such as:
SUM(ISNULL(columnA,0)+ISNULL(columnB,0)).

If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

1 Sep 2006 12:35 AM

onecorp

Dear Mr Wang,

Now the error reads:

Exception Details: System.FormatException: Input string was not in a correct
format.

Regards

Onecorp

(I am in the process of changing the nulls to zeroes to see what effect that
has....will try late today
Show quote

"Charles Wang[MSFT]" wrote:

> Hi Onecorp,
> Nice to meet you again.
>
> This time you bring me a long post. :-)
> If I don't misunderstand your issue, you want to know how to solve the
> problem of SUM(columnA+columnB).
>
> The error should be caused by NULL value in the columns. You can use ISNULL
> to get rid of its influence, such as:
> SUM(ISNULL(columnA,0)+ISNULL(columnB,0)).
>
> If you have any other questions or concerns, please feel free to let me
> know. It's my pleasure to be of assistance.
>
> Charles Wang
> Microsoft Online Community Support
>
> ======================================================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================
>
>

1 Sep 2006 2:04 AM

Charles Wang[MSFT]

Hi Onecorp,
Thanks for your response.

I would like to know the column type. Could you show me the table
definition?
If the column type is int, the writings should be now problem and the error
may be caused by other reasons;
If the column type is char, it need to be converted to Int like
SUM(CAST(ISNULL(column1,'0') as int) + CAST(ISNULL(column2,'0') as int)))

Sincerely,
Charles Wang
Microsoft Online Community Support

1 Sep 2006 4:20 AM

onecorp

Dear Mr Wang,

Details as follows:

Id PK int not null
[1] tinyint null
[2] tinyint null
[4] tinyint null
[5] tinyint null
[6] tinyint null

My sincere aplologies, I changed the data type from int to tinyint.

Onecorp

Show quote

"Charles Wang[MSFT]" wrote:

> Hi Onecorp,
> Thanks for your response.
>
> I would like to know the column type. Could you show me the table
> definition?
> If the column type is int, the writings should be now problem and the error
> may be caused by other reasons;
> If the column type is char, it need to be converted to Int like
> SUM(CAST(ISNULL(column1,'0') as int) + CAST(ISNULL(column2,'0') as int)))
>
> Sincerely,
> Charles Wang
> Microsoft Online Community Support
>
>

1 Sep 2006 10:09 AM

Charles Wang[MSFT]

Hi Onecorp,
Appreciate your information on the table.

Please don't care about that. Tinyint should be also OK from my test in SQL
2000.
The error is probably coming from other places.
Does this issue persist now? If it persists, I would like your mailing me
(chang***@microsoft.com) a simple testable database and your scripts for
reproducing this issue and getting the resolution.

Also, I notice that you also re-opened the old issue. Due to they are
duplicated, I appreicate your understanding that I can only leave one post
open. I would like to leave this brife one open and you can reply me under
this post.

The weekend is coming. Have a great day!

Charles Wang
Microsoft Online Community Support