Use Pivot in SQL Server 2005 instead of looping through CFQUERY

Filed under ColdFusion , SQL , SQL Server

One of the coolest things about ColdFusion is how easy and effortless it is to talk to a database. A simple <cfquery name=”qryTest” datasource=”dsnTest”> and you are connected. With <cfquery> being so easy I find myself using them all the time for comparing data, cfoutput querying quick data for managers, and also for validation of data.

As a young programmer I have learned quickly that you should never ever loop through cfqueries as it is not only bad practice but it is also really really slow (I like my pages built for speed). I have come across some pages showing different techniques to help avoid looping through queries but none helped out until I came across a little built in function in SQLServer 2005 called PIVOT (Thanks Brian Larkin).

If you have never heard of PIVOT I recommend you read this post http://articles.techrepublic.com.com/5100-10878_11-6143761.html. It will explain the basics of PivotTable, its uses, and its limitations.

I want to now explain to you how I put this new feature to use. I am sure there are other ways out there to accomplish this task but I found PIVOT to be the easiest and fastest available.

I had a group of employees that took training tests every month and the managers wanted a quick report of what employees had taken each specific test in the last 30 days and all the employees that had not taken a test. To accomplish this I needed to Right Outer Join tblEmployees so that all employees would display if they had taken a test or not. Then I had to compare this list to the list of employees that were contained in the Training table. Here is a quick sketch of what I wanted the output to look like:

-------------------------------------------------------------------

Bob [X] [X] [ ] [ ] [ ]

Jill [ ] [ ] [X] [ ] [X]

Sue [ ] [ ] [ ] [X] [X]

To avoid looping through my employee list and comparing each employee with the tests taken in the last 30 days (which takes forever) I used a quick PIVOT. Here is the code used:

SELECT EID, first_name, last_name, [test1], [test2], [test3], [test4], [test5]

FROM

(SELECT training_eid,training_test

FROM tblTraining

WHERE DATEDIFF("d", training_observdate, GETDATE()) < 31) AS SourceTable

PIVOT(COUNT(train_job) FOR training_test IN [test1], [test2], [test3], [test4], [test5])) AS PivotTable

RIGHT OUTER JOIN tblEmployees

ON tblEmployees.eid = PivotTable.training_eid

ORDER BY Last_Name, First_Name, EID

«/cfquery>

I highlighted in blue the new PIVOT code. As you can see the select statement grabs all the employees then has placeholders for all of the various tests (1,2,3,4,5). After the FROM statement the PIVOT subquery creates a count of all tests taken by each employee in the last 30 days. The output looks something like this:

-------------------------------------------------------------------

Bob [1] [3] [ ] [ ] [ ]

Jill [ ] [ ] [1] [ ] [2]

Sue [ ] [ ] [ ] [4] [3]

With this output all you have to do is a simple check on the value of each test. Here is a simple output query within an html table that does that:

<tr>

<td bgcolor="#6389D8" class="style8">FIRST_NAME</td>

</tr>

<tr>

<td class="style3">#qryTest.LAST_NAME#</td>

<td class="style3">#qryTest.FIRST_NAME#</td>

</cfif>

</cfoutput>

</table>

The best part of this PIVOT query is that it is so screaming fast and very straight forward. I hope some one finds this as useful as I have.

| View count: 493

Jul21

Post Info

Use Pivot in SQL Server 2005 instead of looping through CFQUERY

CFtips.net