Rotating a Query using GetMetaData() and Loops

I had a query of data that needed to be flipped around (Columns as Rows and Rows as Columns). I had a little bit of trouble figuring out the loops so I wanted to share what Paul Kukiel showed me. Paul's Blog is located here: http://blog.kukiel.net/

So to fully explain the problem I have included 2 tables below. The first table is the output of the data that needs to be rotated and the second is the rotated data.

Original:

Date	DataA	DataB	DataC	DataD
1/26/2009	A	B	C	D
1/27/2009	A	B	C	D
1/28/2009	A	B	C	D

Rotated:

Date	1/26/2009	1/27/2009	1/28/2009
DataA	A	A	A
DataB	B	B	B
DataC	C	C	C
DataD	D	D	D

To start off I am going to assume you have a query of data either through <cfquery> or QueryNew(). Set a MetaData variable by taking the query, which I am going to name myTempQuery, and putting it in the ColdFusion GetMetaData() function.

If you are not familiar with the QueryNew() function information can be found here: AdobeLiveDocs on QueryNew

Now create a couple variables that are going to be used to populate the QueryNew() function. I named them columns and columnsType. After, loop over the columns and columnsType variables setting them to the appropriate column names and types. The loop count should be the number of records (rows) in your query as these rows will now become columns. I created a separate QueryNew(), (qLetter), with a list of the alphabet which I will loop over to name the columns. The alphabet is good because it can be sorted later and also columns can be added if necessary using QueryAddColumn(). Set the column values of the new rotated Query (rotatedQuery).

<cfset qLetter = QueryNew( "" ) />
<cfset QueryAddColumn(qLetter, "name", "CF_SQL_VARCHAR", ListToArray("B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z")) />

Now that the columns are set it is time to populate the rows with the columns of data. We can do this by looping over the columns and then setting the values using QuerySetCell(). The loops are a little tricky to explain but I will give it a try. First, you have to set the number of rows that are going to be popluated in the new rotatedQuery. This is done by using arrayLen() of qMetaData which we created earlier. The arrayLen is the number of columns from the original which are now the rows in the rotated.

Next, we loop over the original columns and set them as rows in the new. This is accomplished by nesting a loop inside of a loop. The loop starts with each column and sets all the data for the column. Then it sets the data into the row using QuerySetCell(). The loop indexes can confuse you so I am highlighting in bold.

Now the query should be rotated properly. You can easily add a column or row if needed. Here is all the code together for anyone that wants to give it a try. I wanna thank Paul for his help ;) Let me know if there is an easier way at accomplishing this task.