Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Single cell vertical sorting of querys

Alternate Sorting

Single cell vertical sorting of querys

by  TruthInSatire  Posted    (Edited  )
How can I output my query in columns not rows?

I've seen this question pop up a couple of times and just happen to have a pretty good example of this. This is good for single cell output, not to display a full row like you'd get doing a simple <cfoutput query = "foo">

It's pretty easy to get your table to do this:
a b c d e
f g h i j
k l m

this FAQ will show you how to display your results like this:
a d g j m
b e h k
c f i l

most of this faq is comments. There isn't really that much code involved. I will try to display that here.

Code: 
[b]<html>
<head>
<title>Vertical Sorting</title>
</head>
<cfquery name = "qMyQuery" datasource = "dsn">
SELECT fields
FROM table
ORDER BY myField
</cfquery>
<body>[/b]
[color gray]<!--- set the number of colums you wish to have --->[/color]
[b]<cfset cols = 5>[/b]
[color gray]<!--- get the number of rows so you know what record to display at the top of the next row. for example if our query contains "a,b,c,d,e,f,g,h,i,j,k,l,m" (13 elements) it will produce 3 totalrows--->[/color]
[b]<cfset totalRows = ceiling(qMyQuery.RecordCount / cols)>[/b]
[color gray]<!--- set inital record to 1 "output" is the actual cell of the query --->[/color]
[b]<cfset output = 1>[/b]
[color gray]<!--- Create table --->[/color]
[b]<table width = "100%" border="0" align="center" cellpadding="2" cellspacing = "2">[/b]	  		
[color gray]<!--- loop through the rows.  This loop will run 3 times in this example --->[/color]
[b]<cfloop from = "1" to = "#totalRows#" index = "thisRow">
<tr>[/b]
[color gray]<!--- this loop will run 5 times in times in this example --->[/color]
[b]<cfloop from = "1" to = "#cols#" index = "thisCol">[/b]
[color gray]<!--- the width in the table cell will dynamicaly calculated to evenly distribute the cells. in this example if cols = 5 100/5 will make the cells 20% of the table --->[/color]
[b]<td width = "<cfoutput>#numberformat(evaluate(100/cols), 99)#</cfoutput>%" align="center" nowrap style = "border: 1px solid #ccc;">[/b]
[color gray]<!--- Check current record with the record count, this will be used to display data or an empty cell --->[/color]
[b]<cfif output lte qMyQuery.recordCount>
<cfoutput>#qMyQuery.myField[output]#</cfoutput>
<cfelse>[/b]
[color gray]<!--- use <br> to display an empty cell --->[/color]
[b]<br>
</cfif>[/b]
[color gray]<!--- increment counter to the next record in this example if we started on the first cell of the first row it would be 1(a), then 4(d), then 7(g) and so on if this was the firs cell on the second row it would be 2(b), 5(e), 8(h), continue... --->[/color]
[b]<cfset output = output + totalRows>
</td>
</cfloop>[/b]
[color gray]<!--- this little bit tells where to start the next row. if we just finished the first row output would be 2(b) --->[/color]
[b]<cfset output = thisRow + 1>
</tr>
</cfloop>
</table>
</body>
</html>[/b]
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top