Grouping Database columns

[kuchiv]

I am trying to group a database column and then print them in a HTML table as a report on the client side. How do I do it?.

[Mhtml]

What do you mean by "group" ?

[kuchiv]

I mean, I need to group a particular field of the database.
For ex,
A table is there with multiple issues of a company
The table should display the company as a single appearance and the rest of details separately. I mean, Company single appearance and the related all fields below that.

[Mhtml]

Aaah, ok I think I get what you are trying to say. :)

Do you want the data to be displayed in this layout?


_____________
| company name |
--------------
| Issues |
--------------

[kuchiv]

yup,
Somewhat in the same manner. but little difference is there.
I want a report in this manner.

CompanyCode Company Name Phone ContactPerson Issues

All these occurances should be single except the issues.
All the issues of a particular company should be displayed without another appearance of the company.
I think I could explain it in a proper manner.

[raf]

First : all records in the recordset have the same fields, so the company name and stuff will be selected for each issue. If you want to create such a table, you need to use some tricks when displaying the data (limiting it that way in the selection) statement, isn’t possible).

3 options:
a. use some database-driven website logic and build a table where each company has 1 line with the number of issues in it. When you select that company, you’re going to another page where you display the company name (taken from the querystring) and run a second select to get all the issues from that company and display them all on the page
b. load in the complete recordset, store it in an array and use some Javascript to display it dynamically (like some dropdown menus
c. load in the recordset; ordered by compagniename, and display it using a loop where you check if the company name is the same as the one from the previous record.


dim previous
previous=”dummystart”
do while rsIssues.EOF=false
response.write("<tr>")
if previous = rsIssues.Fields(“companyname”) then
response.write(“<td></td><td></td><td></td><td></td><td> rsIssues.Fields(“issue”)</td>”) ‘ all blank cells until the issues
else
response.write(“<td> rsIssues.Fields(“companyname”)</td> and so on”)
end if
response.write("</tr>")
previous = rsIssues.Fields(“companyname”)
rsIssues.MoveNext
loop

So you basically loop through the recordset, each issue gets a new line and the company and other info fields are only displayed the first time that the companyname occurs.

Of coarse, you can also use this logic to build another sort of table.

[aCcodeMonkey]

kuchiv,

What database are you using? If you are using SQL you can use Datashaped Queries. The Shaped queries can be pretty powerful once you get a graps of the concept for creating them. I use them to link sever "SubReport" queries together so that I do not have to scroll back and forth thru the recordset or keep applying & removing filters to expose thadata I need.

The query looks a little complicated when you forst look at it but it is just a neet what to beuild a "nested" sub query.

Sample:
Shape{SELECT DISTINCT CompanyName
FROM CompanyInfo
ORDER BY CompanyName ASC}
Append
({SELECT CompanyName, Value1, Value2, Value3
FROM Reports
WHERE (Publish = 1)
ORDER BY Value1 ASC, Value3 DESC}
RELATE CompanyName to CompanyName) AS ChildRecord


You must also specify the correct Data Provider.

oConn.Provider="MSDataShape"

You walk thru the Record to create the report tables s Folows:

oRsParent.ActiveConnection = oConn
oRsParent.Open sSQL
Do While Not oRsParent.EOF

'Start your Parent Table HTML Code Here

' Create and Loop thru the Child Record
set oRsChild=oRsParent("CompanyRecord").Value
Do While Not oRsChild.EOF

'Add your Report Table HTML Code Here

oRsChild.MoveNext
Loop

'Cleanup Child Object
oRsChild.Close
set oRsChild = nothing

oRsParent.MoveNext

'Add Closing your Parent Table HTML Code Here
Loop

' Final Cleanup
oRsParent.close
set oRsParent = nothing
oConn.Close
set oConn = Nothing


Sample [code]
<%
Dim oConn
Dim sSQL
Dim sTotal3,sTotal2,sTotal3,sGrandTotal

sConn="Data Provider=SQLOLEDB; Data Source={Sql Server};Initial Catalog={Database Name};User ID=sa;Password=;"
oConn.Provider="MSDataShape"
oConn.open sConn

sSQL = "Shape{SELECT DISTINCT CompanyName " & _
"FROM CompanyInfo " & _
"ORDER BY CompanyName ASC} " & _
"Append " & _
"({SELECT CompanyName, Value1, Value2, Value3 " & _
"FROM Reports " & _
"WHERE (Publish = 1) " & _
"ORDER BY Value1 ASC, Value3 DESC} " & _
"RELATE CompanyName to CompanyName) AS CompanyRecord"

oRsParent.ActiveConnection = oConn
oRsParent.Open sSQL
do while not oRsParent.EOF
%><table width="500" border="0" cellspacing="0" cellpadding="0">
<tr bgcolor="#CCCCCC">
<td align="center" valign="middle" class="ReportTitle"><%= Trim(oRsParent.Fields(0).Value) %></td>
</tr>
<tr>
<td align="center" >
<% ' Loop thru the Child Record
set oRsChild=oRsParent("CompanyRecord").Value
do while not oRsChild.EOF
sTotal1 = sTotal1 + CInt(oRsChild.Fields("Value1").Value)
sTotal2 = sTotal2 + CInt(oRsChild.Fields("Value2").Value)
sTotal3 = sTotal3 + CInt(oRsChild.Fields("Value3").Value)
%> <table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="left" class="TableHeaderCell" nowrap height="15">Value 1 Title</td>
<td align="left" class="TableHeaderCell" nowrap height="15">Value 2 Title</td>
<td align="left" class="TableHeaderCell" nowrap height="15">Value 3 Title</td>
</tr>
<td align="left" class="DataCell" nowrap><%= oRsChild.Fields("Value1").Value%></td>
<td align="left" class="DataCell" nowrap><%= oRsChild.Fields("Value1").Value%></td>
<td align="left" class="DataCell" nowrap><%= oRsChild.Fields("Value3").Value%></td>
</tr>
<% oRsChild.MoveNext
Loop %>
<tr>
<td><hr color="#666666" size="2" noshade width="100%"></td>
</tr>
</tr>
<td align="left" class="DataCell" nowrap><%= sTotal1 %></td>
<td align="left" class="DataCell" nowrap><%= sTotal2 %></td>
<td align="left" class="DataCell" nowrap><%= sTotal3 %></td>
</tr>

<% oRsChild.close
set oRsChild = nothing
sGrandTotal = sTotal1 + sTotal2 + sTotal3
%> <tr><td align="left" nowrap colspan="3"><hr noshade class="Divider0"></td></tr>
</tr>
<td align="Right" class="DataTitle" nowrap>Grand Total:</td>
<td align="left" class="DataTotalCell" nowrap><%= sGrandTotal %></td>
</tr>
</table>
<% oRsParent.MoveNext %>
</td>
</tr>
</table>
<br>
<hr size="4" color="#990033" noshade>
<br>
<% Loop
oRsParent.close
set oRsParent = nothing
oConn.Close
set oConn = Nothing
%>

MSDN Adding DS to OLEDB (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/oledb/htm/oledbprovinvoking_the_data_shaping_service_for_ole_db.asp)


Hope this helps :cool: