attempting to create crosstab sql

[hsingh1981]

Hi all am trying to create a cross tab query. What am trying to show is list of staff and the equipment names next to them as columns. If they have the equipment then and display it as 'X'.

Can anyone take look at what i've done so far and give me little pointers to achieving my goal?

SELECT     dbo.R_EquipmentArea.EquipID, dbo.R_Equipment.Equipment, dbo.Staff.Firstname, dbo.Staff.Surname
FROM         dbo.EquipTraining INNER JOIN
                      dbo.Staff ON dbo.EquipTraining.StaffID = dbo.Staff.StaffID INNER JOIN
                      dbo.R_Equipment ON dbo.EquipTraining.EquipID = dbo.R_Equipment.ID INNER JOIN
                      dbo.R_EquipmentArea ON 
                      dbo.R_Equipment.ID = dbo.R_EquipmentArea.EquipID EXEC sys_CrossTab '  dbo.EquipTraining INNER JOIN
                      dbo.Staff ON dbo.EquipTraining.StaffID = dbo.Staff.StaffID INNER JOIN
                      dbo.R_Equipment ON dbo.EquipTraining.EquipID = dbo.R_Equipment.ID INNER JOIN
                      dbo.R_EquipmentArea ON dbo.R_Equipment.ID = dbo.R_EquipmentArea.EquipID',
                       'R_EquipmentArea.EquipID', 'R_Equipment.Equipment', 'R_Equipment.Equipment', 'Staff.Firstname, Staff.Surname'

 

[AlexCuse]

for a crosstab read this:

http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

Without seeing your sample data and desired results its hart to say, but I think all you need to do is make your joins to the tables where there could be equipment or not into LEFT joins (not INNER).

Then ,you could do

case when dbo.R_Equipment.Equipment is null then '-' else 'X' end as HasEquipment

in your select.

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page