• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Output data in a gridlike format

Community Beginner ,
May 10, 2022 May 10, 2022

Copy link to clipboard

Copied

 I’m trying to output data in the form of a grid pattern using coldfusion or MySQL . When I execute the code below, it outputs it in the regular drill down format. I Want to see if there’s a ColdFusion method and or a MySQL method that will allow my data to be out in a grid-like format With each row having a different person and each column having the results for that particular person in the row over a period of time. Please see the attached code and please also see the attached desired output. I am trying to see how I can get the desired results to look like the second image. Is it an array function in ColdFusion that I would need to do that in?

 

Attached code:

A981A477-D4AB-4963-A209-9CFB6DBB441B.jpeg



Regular output

329973D8-3F10-4BBF-A21D-DE34FE7F0515.jpeg

 desired output

6A816CA9-79E7-4649-B960-3E43F22E0AFD.jpeg

TOPICS
Advanced techniques

Views

116

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 11, 2022 May 11, 2022

Copy link to clipboard

Copied

SQL Server has an operator that would do that for you, namely, pivot. However, I don't think there is a MySQL equivalent.

 

However, you could use ColdFusion's query-of-a-query to generate the desired output. Then display the result using CSS or an HTML table. An example follows, that uses an HTML table.

 

Let's say your query is called salesQuery. Then you could do something like:

<!--- Query of a query to obtain distinct volumes in ascending order --->
<cfquery dbtype="query" name="vols">
	select distinct(volume) as vol 
	from salesQuery
	order by vol 
</cfquery>

<!--- Query of a query to obtain distinct staff in ascending order --->
<cfquery dbtype="query" name="staffMembers">
	select distinct(staff) as staffMember
	from salesQuery
	order by staffMember
</cfquery>

<cfoutput>
<table border="1">
	<!--- Generate volume title and header--->
	<tr>
		<th>&nbsp;</th>	
		<th colspan="#vols.recordcount#">Volumes</th>
	</tr>
	<tr>
		<th>&nbsp;</th>
		<cfloop query="vols">
			<th>#vols.vol#</th>
		</cfloop>
	</tr>
	
	<cfloop query="staffMembers">
		<tr>
			<td>#staffMembers.staffMember#</td>
			<cfloop query="vols">
				<!--- Sales for given staff for given volume --->
				<td>#getStaffSalesAmountByVolume(staffMembers.staffMember,vols.vol)#</td>
			</cfloop>
		<tr>
	</cfloop>
	
</table>
</cfoutput>

<!--- Function to return the sales amount for given staff for given volume --->
<cffunction name="getStaffSalesAmountByVolume" returntype="numeric" output="no">
	<cfargument name="staffMember" type="string" required="yes">
	<cfargument name="vol" type="numeric" required="yes">
	
	<cfset var sales=queryNew("")>
	
	<!--- Query of a query to get sales amount for given staff and volume --->
	<cfquery dbtype="query" name="sales">
		select sales_amt
		from salesQuery
		where staff='#arguments.staffMember#' and volume=#arguments.vol#
	</cfquery>
	
	<cfreturn sales.sales_amt>
</cffunction>
	

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
May 11, 2022 May 11, 2022

Copy link to clipboard

Copied

Thank you man, I'm really grateful for your reply. I'm going to play with my coding in accordance to what you just put on just see if it works for me. Thanks again for your help

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 16, 2022 May 16, 2022

Copy link to clipboard

Copied

LATEST

Problem solved?

 

Testing above code with the following query:

<cfscript>
salesQuery = queryNew("staff,sales_amt,volume", "varchar,integer,integer");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Christopher Jackson");
querySetCell(salesQuery, "sales_amt", "200");
querySetCell(salesQuery, "volume", "4120");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Christopher Jackson");
querySetCell(salesQuery, "sales_amt", "212");
querySetCell(salesQuery, "volume", "4121");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Christopher Jackson");
querySetCell(salesQuery, "sales_amt", "150");
querySetCell(salesQuery, "volume", "4122");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Christopher Jackson");
querySetCell(salesQuery, "sales_amt", "100");
querySetCell(salesQuery, "volume", "4123");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Christopher Jackson");
querySetCell(salesQuery, "sales_amt", "96");
querySetCell(salesQuery, "volume", "4124");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Kevin Craddock");
querySetCell(salesQuery, "sales_amt", "150");
querySetCell(salesQuery, "volume", "4120");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Kevin Craddock");
querySetCell(salesQuery, "sales_amt", "124");
querySetCell(salesQuery, "volume", "4121");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Kevin Craddock");
querySetCell(salesQuery, "sales_amt", "165");
querySetCell(salesQuery, "volume", "4122");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Kevin Craddock");
querySetCell(salesQuery, "sales_amt", "100");
querySetCell(salesQuery, "volume", "4123");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Kevin Craddock");
querySetCell(salesQuery, "sales_amt", "125");
querySetCell(salesQuery, "volume", "4124");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Timothy Hardin");
querySetCell(salesQuery, "sales_amt", "125");
querySetCell(salesQuery, "volume", "4120");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Timothy Hardin");
querySetCell(salesQuery, "sales_amt", "225");
querySetCell(salesQuery, "volume", "4121");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Timothy Hardin");
querySetCell(salesQuery, "sales_amt", "112");
querySetCell(salesQuery, "volume", "4122");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Timothy Hardin");
querySetCell(salesQuery, "sales_amt", "105");
querySetCell(salesQuery, "volume", "4123");
queryAddRow(salesQuery);
querySetCell(salesQuery, "staff", "Timothy Hardin");
querySetCell(salesQuery, "sales_amt", "114");
querySetCell(salesQuery, "volume", "4124");
//writeDump(salesQuery);	
</cfscript>

 

I get the result:

BKBK_0-1652709021889.png

 

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation