Group Sql results in Row Heading format

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

Re: Group Sql results in Row Heading format

Mike Will via WebmasterKB.com wrote on 02 mrt 2007 in
microsoft.public.inetserver.asp.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,OTHours 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,OTHours from tbl where Project = 'Project2' "&_
"order by OTHours desc"



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

Re: Group Sql results in Row Heading format

Evertjan. wrote:
> Mike Will via WebmasterKB.com wrote on 02 mrt 2007 in
> microsoft.public.inetserver.asp.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,OTHours 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,OTHours 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"

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.

Projects within 10 Miles of <%=Request.QueryString
("qryproject")%>

Re: Group Sql results in Row Heading format

Bob Barrows [MVP] wrote on 02 mrt 2007 in
microsoft.public.inetserver.asp.general:

> Evertjan. wrote:
>> Mike Will via WebmasterKB.com wrote on 02 mrt 2007 in
>> microsoft.public.inetserver.asp.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,OTHours 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,OTHours 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)

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"

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,OTHours 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="Employee Name" & _
"OT Hours"
if isarray(ar) then
CurrProj = ar(0,0)
Response.Write "

Re: Group Sql results in Row Heading format

Bob Barrows [MVP] wrote on 02 mrt 2007 in
microsoft.public.inetserver.asp.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)

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