Search code examples
coldfusioncoldfusion-9cfquery

Transpose query in ColdFusion

I have a simple cfquery which outputs 3 columns with their respective data. The columns are name, address and age.

I want to transpose this set of data so that the names become the columns and the address and age are displayed under each column.

I know that we can use QueryAddColumn or something like this for this issue. Can someone help me out with this problem?

EDIT: Based on the comment below this is the intended output:

Oct 2011          Nov 2011          Dec 2011          Jan 2012          Feb 2012
NumberofPeople    NumberofPeople    NumberofPeople    NumberofPeople    NumberofPeople
EmploymentRate    EmploymentRate    EmploymentRate    EmploymentRate    EmploymentRate
Solution

  • I have included a sample data row at the top where you would put your cfquery statement.

    <cfset firstQuery = queryNew("date,NumberofPeople,EmploymentRate")>
<cfset aRow = queryAddRow(firstQuery)>
<cfset querySetCell(firstQuery,"date","OCT_2011",aRow)>
<cfset querySetCell(firstQuery,"NumberofPeople","28",aRow)>
<cfset querySetCell(firstQuery,"EmploymentRate","50%",aRow)>

<cfset aRow = queryAddRow(firstQuery)>
<cfset querySetCell(firstQuery,"date","NOV_2011",aRow)>
<cfset querySetCell(firstQuery,"NumberofPeople","28",aRow)>
<cfset querySetCell(firstQuery,"EmploymentRate","56%",aRow)>

<cfset aRow = queryAddRow(firstQuery)>
<cfset querySetCell(firstQuery,"date","DEC_2011",aRow)>
<cfset querySetCell(firstQuery,"NumberofPeople","29",aRow)>
<cfset querySetCell(firstQuery,"EmploymentRate","55%",aRow)>

<cfset aRow = queryAddRow(firstQuery)>
<cfset querySetCell(firstQuery,"date","JAN_2012",aRow)>
<cfset querySetCell(firstQuery,"NumberofPeople","30",aRow)>
<cfset querySetCell(firstQuery,"EmploymentRate","52%",aRow)>



<!--- Will Create new query with names as column headers--->
<cfset newQuery = queryNew(valueList(firstQuery.date,','))> 

<!--- Will Create new query with names as column headers--->

<cfset people = queryAddRow(newQuery)>
<cfset rate = queryAddRow(newQuery)>

<cfloop query='firstQuery'>
    <!---Syntax for this function is: QuerySetCell(query, column_name, value [, row_number ]) --->
    <cfset querySetCell(newQuery,firstQuery.date,firstQuery.NumberofPeople,people)>
    <cfset querySetCell(newQuery,firstQuery.date,firstQuery.EmploymentRate,rate)>
</cfloop>

<cfdump var="#newQuery#">

<cfdump var="#ArrayToList(newQuery.getColumnNames())#">

    This is How I would Do it, But I can't think of why I would do it. I'd be interested to hear your use case. Anyway, I hope this helps.

    (P.S This is tested in CF9, so you should be able to copy and paste it to test for yourself.)

    EDIT -(Again):

    Forgot to mention, this can only work if the names your retrieveing from the DB are valid column names, so no spaces (In this example spaces in dates have been replaced by underscores)!

    >>> New code snippet for the updated data structure, the function valueList(firstQuery.date,',') doesn't re-order your columns. The columns are re-ordered on output when dumping. I have used the function ArrayToList(newQuery.getColumnNames()) to show that internally CF maintains the column order and you need only ask it nicely. You should be able to use all this information to nicely output your data how you need it.