Solved

Assigning new value to column based on current query

Posted on 2003-12-07
3
147 Views
Last Modified: 2013-12-24
I'm working with an old database which I have no control over, so I'm stuck to work with it.

There are just three columns: id, topic, and description - designed to be just a single list of topics.  I want to have sub-topics as well and don't need the description field, so I'm using the topic column to store the main topic and the description column to store the sub topics. For main topics, the topic and description are the same.  Hence it looks like this:

id, topic, description
1, New York, New York
2, New York, Manhattan
3, New York, Ithica
4, New York, Long Island
5, California, San Francisco
6, California, California
7, California, Los Angeles

I need the output of the query to be a single list, with the main topics above their associated subtopics. So I was hoping to create a new column (0 if both columns equal, 1 if they don't) so that the output could be ordered correctly. Any idea how to do this? Or is there a better way?
0
Comment
Question by:bududa
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
jyokum earned 500 total points
ID: 9893326
change the datasource and table name to whatever is appropriate.

<cfquery name="qryData" datasource="whatever">
SELECT id, topic, description
FROM tablename
ORDER BY topic, description
</cfquery>

<cfoutput query="qryData" group="topic">
      #qryData.topic#<br />
      <cfoutput>
      <cfif qryData.topic neq qryData.description>
      &nbsp;&nbsp;-&nbsp;&nbsp;#qryData.description#<br />
      </cfif>
      </cfoutput>
</cfoutput>
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9893327
use group by in your query and in the

<cfoutput query="" group="topic">
 #topic# #description#
</cfputput>

if that doesn't do what you want then try this
<cfset strTopic="">
<table>
<cfoutput query="'>
<tr>
<td>
<cfif strTopic neq Topic>
   #topic#
</cfif>
<cfset strTopic=Topic>
</cfif>
</td>
<td>#description#</td>
</tr>
</cfoutput>
</table>
0
 
LVL 25

Expert Comment

by:James Rodgers
ID: 9893332
damn you beat me.... lol ;-)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now