This interesting question came in a few days ago:

What's the easiest way to output a query in columns rather than rows?

At first, I wasn't sure what the reader meant. Did he mean that he only wanted to display one column from a query? I don't think so. But just in case....

<cfloop query="data"> <cfoutput>#name#<br></cfoutput> </cfloop>

Maybe he thought that you needed to output all the values from a query and didn't realize you could just work with one column? Who knows. I've made some pretty silly mistakes in reasoning myself(*).

Then I thought - maybe he wants to "twist" the traditional row by row display of a query and display one column of data as one row of a table. In other words, turn the normal table display around 90 degrees or so. This is also pretty simple. Let's look at the code and then I'll explain it.

<cfset data = queryNew("id,name,age")>

<cfloop index="x" from="1" to="10"> <cfset queryAddRow(data)> <cfset querySetCell(data,"id",x)> <cfset querySetCell(data,"name","User #x#")> <cfset querySetCell(data,"age",randRange(20,90))> </cfloop>

<table border="1"> <cfloop index="col" list="#data.columnList#"> <tr> <cfoutput><td><b>#col#</b></td></cfoutput> <cfloop query="data"> <cfoutput><td>#data[col][currentRow]#</td></cfoutput> </cfloop> </tr> </cfloop> </table>

So, just ignore the beginning. All I'm doing is creating a fake query. I begin my table as you would normally. Now comes the changes. Instead of looping over the query, I loop over the column list. For each column, I start a new table row. I then loop over the query and output only my current column and current row using this syntax:


You may wonder - why didn't I use valueList? For some reason, valueList was built to only allow for a static column. In other words, I would have to hard code, or, etc. You can use Evaluate of course, but that is the tool of the Devil, so I recommend against it.

That's it. If you run this code you will see a nicely transformed query display. Now, I'm not sure why you would do this, but I'm sure there is a good reason. (Of course, half the things I code are for the heck of it and serve no practical purpose!)

*So, what silly mistake did I make? When I was learning AppleSoft BASIC, I read the docs on the syntax, but not very closely. So, I'd write a line of BASIC, and then start on a new line. I'd start on new line by hitting the space bar until the cursor wrapped to a new line. Ok, you can sto laughing now, I was only 12 or so!