Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Cold Fusion cfquery output from a Pivot

Posted on 2004-09-14
6
Medium Priority
?
957 Views
Last Modified: 2012-06-21
I am relatively new to Cold Fusion Studio 5. I have had success with hundreds of pages serving up on our company intranet.. but..
I wish to output the results of my SQL crosstab (pivot) query in Cold fusion

I am happy with how the sql query looks in MSAccess

But I need the cold fusion code to output this. I'll supply the code if you like .. max points

 
0
Comment
Question by:Warez_Willy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 4

Expert Comment

by:willcode4coffee
ID: 12179644
Yes, please post the code for this query.
0
 

Author Comment

by:Warez_Willy
ID: 12185415
OK here is my sql statement from msaccess

TRANSFORM Count(Stock.TicketNumber) AS CountOfTicketNumber
SELECT Stock.Length
FROM (Categories INNER JOIN StockDescription ON Categories.CategoryID = StockDescription.CategoryID) INNER JOIN Stock ON StockDescription.StockDescriptionID = Stock.StockDescription
WHERE (((Categories.CategoryID)=14) AND ((Stock.StatusUsed)=0) AND ((Stock.Location)='ASH'))
GROUP BY Categories.Category, Stock.Length, Categories.CategoryID, Stock.StatusUsed, Stock.Location
ORDER BY Stock.Length
PIVOT StockDescription.StockDescription;


I'd like the results of this cross tab in a grid format in cold fusion. Thanks very much.
0
 

Expert Comment

by:nicky2k
ID: 12285432
Please confirm, do you want a CF tabular grid or a graphical grid?

A table is easy, use CF arrays.

<cfset myArray=ArrayNew(2)> <!--creates 2 dimensional array-->

<cfoutput query="yourqueryname">
<cfset myArray[1][#CurrentRow#]=#yourqueryname.StockCategory#>
<cfset myArray[2][#CurrentTow#]=#yourqueryname.StockDescription#>
</cfoutput>

<!--note that CurrentRow is a valid cfoutput query variable that just dynamically calculates the current row of the record you are retreiving--->

If you are after multidimensional sort of grid (I need you to explain precisely here), you might want to do an array of arrays or use structures.

Nicky.

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Warez_Willy
ID: 12293294
Thanks nicky2k i believe arrays are the direction i need to go. But my problem still isn't resolving using your suggestion(ps ..."currentTow" is a typo yes?)

I feel a bit thick here... but, do I use the same query I posted (ie the pivot type, or do I use a simpler SELECT query and let the CF "array" function sort it out?

If so I would have a query consisting of 3 columns

StockDescription - Length - Countofticketnumber

I would like the results in a grid if possible.
where Length is the row heading, Stockdescription is the column heading and countofticketnumber is the data
0
 

Accepted Solution

by:
nicky2k earned 1500 total points
ID: 12295656
Yes, CurrentTow is a typo, it should be Current Row.

If you wanted a graphical grid, then use <CFgrid>.  You need to be doing something like this:-

<!--- This example shows the cfgrid, cfgridCOLUMN, cfgridROW,
and cfgridUPDATE tags in action --->

<!--- use a query to show the useful qualities of cfgrid --->

<!--- If the gridEntered form field has been tripped,
perform the gridupdate on the table specified in the database.
Using the default value keyonly=yes allows us to change only
the information that differs from the previous grid --->
<CFIF IsDefined("form.gridEntered") is True>
<cfgridUPDATE GRID="FirstGrid" DATASOURCE="yourdatasource"
TABLENAME="Stocks"  KEYONLY="Yes">
</CFIF>


<cfquery name="yourqueryname" datasource="yourdatasource">
SELECT Stock.Length, Count(Stock.TicketNumber) AS CountOfTicketNumber,StockDescription.StockDescription
FROM (Categories INNER JOIN StockDescription ON Categories.CategoryID = StockDescription.CategoryID) INNER JOIN Stock ON StockDescription.StockDescriptionID = Stock.StockDescription
WHERE (((Categories.CategoryID)=14) AND ((Stock.StatusUsed)=0) AND ((Stock.Location)='ASH'))
GROUP BY Categories.Category, Stock.Length, Categories.CategoryID, Stock.StatusUsed, Stock.Location
ORDER BY Stock.Length
</cfquery>

<HTML>
<HEAD>
<TITLE>
cfgrid Example
</TITLE>
</HEAD>

<BODY>
<H3>cfgrid Example</H3>

<I>Try adding stock to the database, and then deleting it.</I>
<!--- call the CFFORM to allow us to use cfgrid controls --->
<CFFORM ACTION="yourpage.cfm" METHOD="POST" ENABLECAB="Yes">

<!--- We include Course_ID in the cfgrid, but do not allow
for its selection or display --->
<!--- cfgridCOLUMN tags are used to change the parameters
involved in displaying each data field in the table--->

<cfgrid NAME="FirstGrid" WIDTH="450"
    QUERY="yourqueryname" INSERT="Yes"
    DELETE="Yes" SORT="Yes"
    FONT="Tahoma" BOLD="No" ITALIC="No"
    APPENDKEY="No" HIGHLIGHTHREF="No"
    GRIDDATAALIGN="LEFT" GRIDLINES="Yes"
    ROWHEADERS="Yes" ROWHEADERALIGN="LEFT"
    ROWHEADERITALIC="No" ROWHEADERBOLD="No"
    COLHEADERS="Yes" COLHEADERALIGN="LEFT"
    COLHEADERITALIC="No" COLHEADERBOLD="No"
    SELECTCOLOR="Red" SELECTMODE="EDIT"
    PICTUREBAR="No" INSERTBUTTON="To insert"
    DELETEBUTTON="To delete" SORTASCENDINGBUTTON="Sort ASC"
    SORTDESCENDINGBUTTON="Sort DESC">
      <cfgridCOLUMN NAME="Length" HEADER="Stock Length"
        HEADERALIGN="LEFT" DATAALIGN="LEFT"
        BOLD="Yes" ITALIC="No"
        SELECT="Yes" DISPLAY="Yes"
        HEADERBOLD="No" HEADERITALIC="Yes">
    <cfgridCOLUMN NAME="Description" HEADER="Description"
        HEADERALIGN="LEFT" DATAALIGN="LEFT"
        BOLD="No" ITALIC="No"
        SELECT="Yes" DISPLAY="Yes"
        HEADERBOLD="No" HEADERITALIC="No">
    <cfgridCOLUMN NAME="CountOfTicketNumber" HEADER="Count"
        HEADERALIGN="LEFT" DATAALIGN="LEFT"
        FONT="Times" BOLD="No"
        ITALIC="No" SELECT="Yes"
        DISPLAY="Yes" HEADERBOLD="No"
        HEADERITALIC="No">
</cfgrid>

</cfform>

That is the general idea. You may have to read up on the <CFGrid> tag, use help,Open Help References Window  and then type cfgrid in the text box in your CF Studio. You will find that information very useful, play around with the examples they give.

Hope that helps.

Nicky.
0
 

Expert Comment

by:nicky2k
ID: 12295693
By the way, COURSE_ID should say Stock_ID.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question