Quering rows in a table and outputing on single line

[mike bonjourno]

Hi,

I would really appreciate any help with the following:

I want to use a single query to extract the data below (this is a small
sampling of the actual column list).
Initially the results will be ordered by EmployeeName.
The last line will contain the average days missed for these results - I can
do this in a separate
query if necessary.

ISSUES:
I need to be able to sort the data (ASC/DESC) by any/every column.
The db table cannot be changed because of other programs using the same data.
This data will be queried 100's of times per day by various users, so
efficiency is a must.

DB TABLE:

EmployeeName EmpDaysMissed EmpWorkYear
Fred Mertz 004 2004
Fred Mertz 012 2005
Lucy Ricardo 112 2004
Lucy Ricardo 087 2005
Ricky Ricardo 001 2004
Lucy Ricardo 099 2003

OUTPUT RESULTS: - results will be formatted inside an HTML table

EmployeeName YR05 DM05 YR04 DM04 YR03 DM03
Fred Mertz 2005 012 2004 004 2003 (blank)
Lucy Ricardo 2005 087 2004 112 2003 099
Ricky Ricardo 2005 012 2004 004 2003 (blank)
AVERAGES: 037 040 099

If there isn't any data for a particular year I need to leave that cell blank
in the html table.

The actual query will contain at least 10,000 records, therefore I can't run
another query for each
employee during the cfoutput query statement.

There is a mainframe that sends a new file to the SQL server each night.
SQL then deletes the current year data (in this case 2005) and uploads the
flat file containing the most current data on the employees.

[Dan Bracuk]

I think the best way to approach this is to scrunch your query into a 2D array
and then output the array. That way it will continue to work after the
available years change.

It will take some trial and error (not mine of course) but the general idea
will be:

First, change the order by clause of your query so that it sorts by EmployeeID
then by year, or whatever you call those fields. Next, do a Q of Q where you
select the distinct year, ordered by year. You can also use Q of Q to get your
averages.

The first row of your array will be your display headers. Element[1]1[1] will
be "Employee Name". You can loop through the first Q of Q to generate the rest
of them.

Then do something like this (I am going to use cfscript syntax)
ThisGuy = MainQuery.EmployeeName[1];
ThisRow = 2; // row 1 has the headers
Years = ValueList(QofQ.year); // saves typing
for (ii = 1, ii lte MainQuery.Recordcount; ii = ii +1) {
if (MainQuery.EmployeeName[ii] is not ThisGuy) {
ThisRow = ThisRow + 1;
myArray[ThisRow][1] = ThisName;
ThisGuy = MainQuery.EmployeeName[ii];
}
// 1st year has columns 2 and 3, 2nd has 4 and 5, etc
Col1 = ListFind(Years, MainQuery.year[ii]) + 2;
Col2 = Co1 + 1;
MyArray[ThisRow][Col1] = MainQuery.Year[ii];
MyArray[ThisRow][Col2] = MainQuery.DaysAway[ii];
}

But without any errors

[MikerRoo]

Never do this kind of thing by looping and multiple queries.

This is a very common problem and you will find much help on the SQL forums.
For example [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216[/url]

If you are on SQL server 2005, it now has a PIVOT built into T-Sql.

For SQL server 2000 or earlier, you can also easily link into Access and use
transform to generate a cross tab query.

[philh]

There's also a pivot workaround for SQL2K. Reply if you want the code, or check [url]www.sqlteam.com[/url] in the Forums there.