Parameters
| QUERY | Required. The query that you want to transpose. Could be the result of a CFQUERY or any other CF tag that generates a query result set. |
| NEWQUERY | Optional. A name for the new, transposed version of the query. If ommitted, the original query is overwritten (replaced) with the new, transposed version. |
| ADDLABELSFROM |
Optional. One of the columns from the original query.
If ADDLABELSFROM is provided, then values from each row of the original query will be used to create column names for the new query. The column names will consist of the column you provide here, then an underscore, then the value of each row from the corresponing column in the original query (see example 2). Default is No. NOTE: If you use this option, it is your responsibility to ensure that the resulting column names will be "legal" variable names to CF. Meaning that the values in the original query for the column you specify for ADDLABELSFROM must contain only numbers, letters, or underscores. |
| ADDCOLUMNNAMES | Optional. Yes or No. If ADDCOLUMNNAMES="Yes", then the column names from the original query will be added to the first column of each row of the new query (see example 3). If ADDCOLUMNNAMES="No" or is ommitted, then the columns of the new query will just be named Col1, Col2, Col3, and so on. |
| TOPLEFTLABEL | Optional. If ADDCOLUMNNAMES="Yes", then you may provide text here that will become the column name for the first column of the new query (the column that has the original column names for its rows). If ommitted, the default label of "Columns" will be used. |
Examples
For the following examples, assume that the following CFQUERY tag has been executed:
| CUSTOMERID | COMPANY | FIRSTNAME | MIDDLEINIT | LASTNAME | ADDRESS1 | ADDRESS2 | CITY | STATE | ZIP | PHONE | CUSTOMERSINCE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ABC Company | Arnold | B | Cooper | 123 West 1st Street | 1st Floor | Anywhere | NY | 00001 | 212 555 1212 | acooper@abc.com | 1996-07-16 00:00:00 |
| 2 | DEF Company | Diana | E | Fontaine | 456 East 2nd Street | Suite 1200 | Anyplace | NY | 00002 | 718 555 1212 | dfontaine@def.com | 1995-05-05 00:00:00 |
| COL1 | COL2 |
|---|---|
| 1 | 2 |
| ABC Company | DEF Company |
| Arnold | Diana |
| B | E |
| Cooper | Fontaine |
| 123 West 1st Street | 456 East 2nd Street |
| 1st Floor | Suite 1200 |
| Anywhere | Anyplace |
| NY | NY |
| 00001 | 00002 |
| 212 555 1212 | 718 555 1212 |
| acooper@abc.com | dfontaine@def.com |
| 1996-07-16 00:00:00 | 1995-05-05 00:00:00 |
| CUSTOMERID_1 | CUSTOMERID_2 |
|---|---|
| 1 | 2 |
| ABC Company | DEF Company |
| Arnold | Diana |
| B | E |
| Cooper | Fontaine |
| 123 West 1st Street | 456 East 2nd Street |
| 1st Floor | Suite 1200 |
| Anywhere | Anyplace |
| NY | NY |
| 00001 | 00002 |
| 212 555 1212 | 718 555 1212 |
| acooper@abc.com | dfontaine@def.com |
| 1996-07-16 00:00:00 | 1995-05-05 00:00:00 |
| DATA | CUSTOMERID_1 | CUSTOMERID_2 |
|---|---|---|
| CUSTOMERID | 1 | 2 |
| COMPANY | ABC Company | DEF Company |
| FIRSTNAME | Arnold | Diana |
| MIDDLEINIT | B | E |
| LASTNAME | Cooper | Fontaine |
| ADDRESS1 | 123 West 1st Street | 456 East 2nd Street |
| ADDRESS2 | 1st Floor | Suite 1200 |
| CITY | Anywhere | Anyplace |
| STATE | NY | NY |
| ZIP | 00001 | 00002 |
| PHONE | 212 555 1212 | 718 555 1212 |
| acooper@abc.com | dfontaine@def.com | |
| CUSTOMERSINCE | 1996-07-16 00:00:00 | 1995-05-05 00:00:00 |