Group Sql results in Row Heading format

[Mike Will via WebmasterKB.com]

I've been struggling with this for a while now. But I want to display my
query results in a table where they are grouped by project. I am using a do
while loop but it not achieving the look I want.

This is what I got:
Project Employee Name OT Hours
Project 1 Employee A 7
Project 1 Employee C 5
Project 1 Employee D 3
Project 2 Employee A 8
Project 2 Employee B 4
Project 2 Employee E 6

This is what I would like or as it has been expressed to me is needed:
Project 1
Employee Name OT Hours
Employee A 7
Employee C 5
Employee D 3

Project 2
Employee Name OT Hours
Employee A 8
Employee B 4
Employee E 6

--
Message posted via WebmasterKB.com

http://www.webmasterkb.com/Uwe/Forums.aspx/asp/200703/1

[Evertjan.]

Re: Group Sql results in Row Heading format

Mike Will via WebmasterKB.com wrote on 02 mrt 2007 in
microsoft.publi c.inetserver.as p.general:

I've been struggling with this for a while now. But I want to display
my query results in a table where they are grouped by project. I am
using a do while loop but it not achieving the look I want.
>
This is what I got:
Project Employee Name OT Hours
Project 1 Employee A 7
Project 1 Employee C 5
Project 1 Employee D 3
Project 2 Employee A 8
Project 2 Employee B 4
Project 2 Employee E 6
>
This is what I would like or as it has been expressed to me is needed:
Project 1
Employee Name OT Hours
Employee A 7
Employee C 5
Employee D 3

sql = "select EmployeeName,OT Hours from tbl where Project = 'Project1' "&_
"order by OTHours desc"

>
Project 2
Employee Name OT Hours
Employee A 8
Employee B 4
Employee E 6

sql = "select EmployeeName,OT Hours from tbl where Project = 'Project2' "&_
"order by OTHours desc"



--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

Re: Group Sql results in Row Heading format

Evertjan. wrote:

Mike Will via WebmasterKB.com wrote on 02 mrt 2007 in
microsoft.publi c.inetserver.as p.general:
>

>I've been struggling with this for a while now. But I want to
>display my query results in a table where they are grouped by
>project. I am using a do while loop but it not achieving the look I
>want.
>>
>This is what I got:
>Project Employee Name OT Hours
>Project 1 Employee A 7
>Project 1 Employee C 5
>Project 1 Employee D 3
>Project 2 Employee A 8
>Project 2 Employee B 4
>Project 2 Employee E 6
>>
>This is what I would like or as it has been expressed to me is
>needed: Project 1
>Employee Name OT Hours
>Employee A 7
>Employee C 5
>Employee D 3

>
sql = "select EmployeeName,OT Hours from tbl where Project =
'Project1' "&_ "order by OTHours desc"
>

>>
>Project 2
>Employee Name OT Hours
>Employee A 8
>Employee B 4
>Employee E 6

>
sql = "select EmployeeName,OT Hours from tbl where Project =
'Project2' "&_ "order by OTHours desc"

You're not serious are you?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Mike Will via WebmasterKB.com]

Re: Group Sql results in Row Heading format

Thank you but I would hope there is a more efficient way.

Here is my code where I have begun building a table of information from my
recordset. I'm sure I just need some help thinking this through.

<table width="100%">
<%If rs2.RecordCount = 0 Then%>
<tr>
<td colspan="9" bgcolor="#ECE9D 8" id="headtext">< h3>No projects within 10
miles <%=Request.Quer yString("qryPro ject")%></h3></td>
</tr>
</table>
<%rs2.Close
Else%>
<tr>
<td colspan="9"><h4 >Projects within 10 Miles of <%=Request.Quer yString
("qryproject")% ></h4></td>
</tr>
<tr bgcolor="#0099F F">
<th width="20%">Pro ject</th>
<th width="20%">Emp loyee</th>
<th width="20%">Pro ject Description</th>
<th width="20%">Ove rtime Balance</th>
<th width="20%">Pri mary Contact Number</th>
</tr>
<% 'Loop through the recordset
Do While not rs2.EOF
'Write the HTML to display the current record in the recordset%>
<table width="100%">
<tr bgcolor="#CCCCC C">
<td width="20%" align="center"> <%=rs2("Within1 0Miles")%></td>
<td width="20%" align="center"> <%=rs2("Employe e")%></td>
<td width="20%" align="center"> <%=rs2("10MileD esc")%></td>
<td width="20%" align="center"> <%=rs2("OTBal") %></td>
<td width="20%" align="center"> <%=rs2("Phone1" )%></td>
</tr>
</table>

--
Message posted via WebmasterKB.com

http://www.webmasterkb.com/Uwe/Forums.aspx/asp/200703/1

[Evertjan.]

Re: Group Sql results in Row Heading format

Bob Barrows [MVP] wrote on 02 mrt 2007 in
microsoft.publi c.inetserver.as p.general:

Evertjan. wrote:

>Mike Will via WebmasterKB.com wrote on 02 mrt 2007 in
>microsoft.publ ic.inetserver.a sp.general:
>>

>>I've been struggling with this for a while now. But I want to
>>display my query results in a table where they are grouped by
>>project. I am using a do while loop but it not achieving the look I
>>want.
>>>
>>This is what I got:
>>Project Employee Name OT Hours
>>Project 1 Employee A 7
>>Project 1 Employee C 5
>>Project 1 Employee D 3
>>Project 2 Employee A 8
>>Project 2 Employee B 4
>>Project 2 Employee E 6
>>>
>>This is what I would like or as it has been expressed to me is
>>needed: Project 1
>>Employee Name OT Hours
>>Employee A 7
>>Employee C 5
>>Employee D 3

>>
>sql = "select EmployeeName,OT Hours from tbl where Project =
> 'Project1' "&_ "order by OTHours desc"
>>

>>>
>>Project 2
>>Employee Name OT Hours
>>Employee A 8
>>Employee B 4
>>Employee E 6

>>
>sql = "select EmployeeName,OT Hours from tbl where Project =
> 'Project2' "&_ "order by OTHours desc"

>
You're not serious are you?
>

Sorry?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

Re: Group Sql results in Row Heading format

Evertjan. wrote:

>>
>You're not serious are you?
>>

>
Sorry?

You ARE serious? You would actually run separate sql statements for each
project ID? Rather than retrieving them all and looping to display them in
groups?

To the OP: I will post a script to do this later - it's simply a matter of
keeping track of which project ID you are on when displaying the items.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

Re: Group Sql results in Row Heading format

Mike Will via WebmasterKB.com wrote:

I've been struggling with this for a while now. But I want to
display my query results in a table where they are grouped by
project. I am using a do while loop but it not achieving the look I
want.
>
This is what I got:
Project Employee Name OT Hours
Project 1 Employee A 7
Project 1 Employee C 5
Project 1 Employee D 3
Project 2 Employee A 8
Project 2 Employee B 4
Project 2 Employee E 6
>
This is what I would like or as it has been expressed to me is needed:
Project 1
Employee Name OT Hours
Employee A 7
Employee C 5
Employee D 3
>
Project 2
Employee Name OT Hours
Employee A 8
Employee B 4
Employee E 6

I'm assuming your sql statement looks something like this:

sql = "select Project, EmployeeName,OT Hours from tbl "&_
"order by Project ,OTHours desc"

I would use GetRows to read the recordset into an array and process it like
this:

dim rs, ar, CurrProj, NewProj

set rs=conn.execute (sql,,1)
if not rs.eof then ar=rs.GetRows
rs.close:set rs=nothing
conn.close: set conn=nothing
dim titles
titles="<tr><th >Employee Name</th>" & _
"<th>OT Hours</th></tr>"
if isarray(ar) then
CurrProj = ar(0,0)
Response.Write "<table border=""1"" style=""" & _
"bordercollapse :collapse""><tr ><th colspan=2>" & _
CurrProj & "</th></tr>"
Response.Write titles
for i=0 to ubound(ar,2)
NewProj=ar(0,i)
if CurrProj <NewProj then
CurrProj=NewPro j
Response.Write "<tr><th colspan=2>" & _
CurrProj & "</th></tr>"
Response.Write titles
end if
Response.Write "<tr><td>" & ar(1,i) & "</td>" & _
"<td>" & ar(2,i) & "</td></tr>"
next
Response.Write "</table>"
else
Response.Write "No Records Were Retrieved"
end if



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Evertjan.]

Re: Group Sql results in Row Heading format

Bob Barrows [MVP] wrote on 02 mrt 2007 in
microsoft.publi c.inetserver.as p.general:

Evertjan. wrote:

>>>
>>You're not serious are you?

>>
>Sorry?

>
You ARE serious? You would actually run separate sql statements for
each project ID? Rather than retrieving them all and looping to
display them in groups?

Sure, but as in all developing you have to take it one step at a time, Bob,
and building a page with two or three "project" groups as discrete SQL's
will convince you [general you] what you want is feasable.

Then you can choose for a system whith an outer and an inner loop.

and finally make a develish comlicated SQL that can do the whole
kakamadoodle in one go.

I am sure you [specific you], Bob, can skip those intermediate steps in
your head, but will that be educational?

To the OP: I will post a script to do this later - it's simply a
matter of keeping track of which project ID you are on when displaying
the items.

I am sure most OP's will be glad for that, and they will not loose any
sleep, but will they be able to do the job themselves next time?

And no, I am not totally serious, where would the fun be in that?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Mike Will via WebmasterKB.com]

Re: Group Sql results in Row Heading format

Yes that is what my sql statement looks like. I experimented with an array
and just couldn't pull it off. I'm not above getting help so I posted the
question. Thanks Bob I'll give it a shot.

--
Message posted via WebmasterKB.com

http://www.webmasterkb.com/Uwe/Forums.aspx/asp/200703/1