Query output to multiple columns (list?)

[health-pact]

I have a query that extracts data about companies, people and offices.  After I display the company info, I want to display the people/office data in 2 columns, with a list of people on the left and offices on the right.  There is no correlation between the 2 columns, i.e. I will have a list of all of the company's people on the left, and all of the company's offices on the right.

I can do this with cfoutput into a table with 2 TD areas, and this works fine for a company with 1 employee and 1 office, a matching number of employees and offices, and 1 of either with multiples of the other.  If I do this output with non-matching multiples of both, I end up with wierd looking lists with data that does not show up side by side.

I was told that the best way for me to do this is with Lists, but I cannot seem to find any clear instructions, or good examples, on how to do this.

Anyone? 

[Dan_Bracuk]

What does the initial record set look like?

[health-pact]

Company.Name, Company.City, etc, Employee.LastName, Employee.FirstName, Employee.MiddleName, etc., Office.Address, Office.City, Office.Zip, etc.  All of this data is extracted in the same query.

[Dan_Bracuk]

Would a subset of a dump look anything like this?

companyname     employeename     officecity

abc                     Jim                      Toronto

abc                     John                     Montreal

abc                     Jane                     Toronto

Also, can we assume that employees work in only one office and each office has at least one employee?  In other words, the number of offices will never exceed the number of people?

[health-pact]

The dump is a good example of the output.

Employees and offices are not really inter-related.  An employee could work in one, more than one, or all offices, and the number of offices does sometimes exceed the number of employees.  e.g. I have one company with 2 employees and 5 offices, and another with 10 employees and 6 offices.  I'm thinking that maybe the combined query, because of the potential for different numbers of employees and offices, could be causing a problem, and I may have to do this with three separate queries.

[Dan_Bracuk]

You need one database query and 2 Q of Qs.  Let's call them AllRecords, DistinctPeople, and DistinctCities.

if (DistinctPeople.recordcount gte DistinctCities.recordcount)

rows = DistinctPeople.recordcount;

else

rows = DistinctCities.recordcount;

<cfoutput>

<cfloop from = "1" to = "#rows#" index = "row">

<tr>

<td>

<cfif DistinctPeople.recordcount lte row>#ListGetAt(ValueList(DistinctPeople), row)#<cfelse> </cfif>

</td>

repeat for offices

close tags

[health-pact]

I tried this in many different configurations, but could not get it to work properly.  I ended up using Lists (ListFind, ListGetAt) to do it with help from a more experienced programmer.