Need help in Pivot Query construction.

See more:

SQL-Server

SQL-Server-2008

Hi ,

I need some help in making a PIVOT query for the following situation:
My data looks like this:

    ID   Participant  Representative  Manager
1   100  Orange       Mango           Banana
2   200  Mango        Banana          Orange
3   300  Null         Null            Mango
4   400  Banana       Null            Null
5   500  Orange       Banana          Null

I want it the following way:

    ID   Participant-Orange Participant-Mango Participant-Banana....
1   100   1                 
2   200                          1                                 
3   300                    
4   400                                             1             
5   500   1             

and so on for the combinations of Representatives and Manager and their count below it for Orange,Banana,Mango..

Tried doin it using Pivot Query but that can only help me create headers this way..the count for all shows null which is not what i want.
Can someone please help me with the query to achieve this dataset.

[edit] removed 'Urgent!' from subject [/edit]

[edit]Code block added, "Ignore HTML..." option disabled, subject length increased to 30+ characters- OriginalGriff[/edit]