2 Column Display of Recordset in Web Page

[Jay Scherberth]

I need to display the results of a query in two columns (SELECT *). Do I
need to create two distinct recordsets or is there some way to loop through
a single recordset so as to display, for example, all odd numbered records
is column 1 and even numbered records in column 2?

Thank you

 

[Dan Artuso]

Hi,
Yes, you can loop through the recordset and do this.
I'll give an example, but i'll leave it to you to write the
ASP to generate your HTML table.

i = 1

Do While Not rs.EOF
If i Mod 2 <> 0 'we have an odd one
'put in 1st column
Else
'put in 2nd column
End If
i = i + 1
rs.MoveNext
Loop

 

[Duane Hookom]

Hi Jay,
You could use a couple queries (one for odd records and one for even) and
then combine them:
The following is SQL that would pull all the Even records while calculating
the "Pair" number using the Customer table from Northwind.
===qselEven=====
SELECT
(SELECT Count(*)
FROM Customers C
WHERE C.CustomerID<=Customers.CustomerID) Mod 2 AS OddEven,
(SELECT Count(*)
FROM Customers C
WHERE C.CustomerID<Customers.CustomerID)\2 AS Pair,
Customers.*
FROM Customers
WHERE (SELECT Count(*) FROM Customers C WHERE
C.CustomerID<=Customers.CustomerID) Mod 2=0;
===qselOdd==================
SELECT
(SELECT Count(*)
FROM Customers C
WHERE C.CustomerID<=Customers.CustomerID) Mod 2 AS OddEven,
(SELECT Count(*)
FROM Customers C
WHERE C.CustomerID<Customers.CustomerID)\2 AS Pair,
Customers.*
FROM Customers
WHERE (SELECT Count(*) FROM Customers C WHERE
C.CustomerID<=Customers.CustomerID) Mod 2=1;

Combine these two in a select query joining the "Pair" field. I used a RIGHT
JOIN since there may be more odd records than even records.

SELECT qselOdd.OddEven AS Odd, qselEven.OddEven AS Even, qselOdd.CompanyName
AS OddCompany, qselOdd.CustomerID AS OddID, qselEven.CompanyName AS
EvenCompany, qselEven.CustomerID AS EvenID
FROM qselEven RIGHT JOIN qselOdd ON qselEven.Pair = qselOdd.Pair;

BTW: Can I put this on my timesheet?

 

[Jay Scherberth]

Duane,

It's truly a small world here in cyberspace. This is actually for a personal
project but I'll be sure to put this code in my toolbox.

Thank you for the example. Now I know where to find you ;-)

Regards,

- Jay