cross tab query

[hsingh1981]

Hi all,

I'm try to show query which have staff names and there grades and show all the equipment they used. I can do that with this code:

SELECT     Staff.Surname + ', ' + Staff.Firstname AS StaffName, Staff.Grade, R_Equipment.Equipment
FROM         R_EquipmentArea INNER JOIN
                      R_Equipment INNER JOIN
                      EquipTraining INNER JOIN
                      Staff ON EquipTraining.StaffID = Staff.StaffID ON R_Equipment.ID = EquipTraining.EquipID ON R_EquipmentArea.EquipID = R_Equipment.ID
GROUP BY Staff.Surname + ', ' + Staff.Firstname, Staff.Grade, R_Equipment.Equipment

But wat i would like to see is equipment as the column names and if they do have the equipment mark it as an "X"

eg.
staffname grade ventilator defib heartmonitor

joe blogs 5 x
andrew smith 4 x
james tee 6 x
Craig james 4 x
etc

Am not sure how to do this with sql 2000? can someone show me an example...or something

many thanks

 

[Qik3Coder]

I'm going to start you down the path, cause I can't think clearly at the moment.

I'm assuming that you have an undetermined amount of equipment types. Anytime you have more than a couple, assume that someone is going to add a new piece as soon as your code hits production.


You are going to need a temp table with all the different types of equipment in it. Then you are going to need to dynamically create another temp table, with the column names in the first temp table.

Then in your dynamically created select statement, you are going to need a:
case when .Equipment = @EquimentName Then X else 0 end as @EquipmentName

Good luck, once you have something put together, and need more help, post back.


-Sometimes the answer to your question is the hack that works