Present table data rotated

See more:

SQL-Server

T-SQL

i want to present the below table with a 90 degree rotate in the anti clock wise direction, so tables column name fields will change to category type and for each category the other field value will below that. How can i write Tsql for this to show result from below table in other temp table.

S.NO First_name      last_name   SignUpID  Rol_Name      EventDate category
1   User1Name   User1Name   12  90 Day Client   11/01/2011  DC
2   User1NameAtt1   User1NameAtt1   12  90 Day Client   11/01/2011  DC
3   User4Name   User4Name       13  Student Client  11/01/2011  ST
4   User4NameAtt1   User4NameAtt1   13  Student Client  11/01/2011  ST
5   user1           user1           14  90 Day Client   11/01/2011  DC
6   att1            att1            14  Employee-Acco   11/01/2011  AD
7   User4NameAtt2   User4NameAtt2   15  Student Client  11/01/2011  ST
8   easdsd          ertrt           15  90 Day Client   11/01/2011  DC
9   User4NameAtt3   User4NameAtt3   15  Employee-Acco   11/01/2011  AD

Above result i am getting after running some complex sql query.
Now i want to rearrange this resut set in other form in the table.
Using Tsql how can i arrange this result set in the form, i am presenting below? I want to GroupBy(SignUpID) and want to display result in a table in the form that all category name in the head for a pericular, from the above result set and below that first_name+last_name for all common signUpId in a row, and in the same row i want to add all category type with an additional column for the count of each category in the same row.
I am presenting a picture below too.

S.NO DC DC DC ST ST ST AD AD AD DC ST AD CA SP Asociate CA CA CA CA CA SPOUS SPOUS

I want to make exactly a table structure of above type for all my query result set. Each field is a column in above line. And one distinguish column for each category type in the the table will give count for each category type in each row.

I am using sql server 2005 and trying to convert that table in the form i am displaying above but no success, please help me out with this, how should i make query to fetch result of desire form, after which i will take that in temporary table and will bind with my grid view from the front hand. If there any other best solution please suggest me that too.

This is what i have done to fetch the above result, now i want to present it in the form i am asking you.

select Row_number() OVER(ORDER BY (SELECT 1)) AS 'Serial Number',
EP.FirstName,Ep.LastName, Ep.SignUpID, [dbo].[GetBookingRoleName](ES.UserId,EP.BookingRole) as RoleName,
(select top 1 convert(varchar(10),eventDate,103)from [3rdi_EventDates] where EventId=@ItemId) as EventDate,
(CASE [dbo].[GetBookingRoleName](ES.UserId,EP.BookingRole)
WHEN 'Employee - Marketing' THEN 'DC'
WHEN 'Employee - Accounting' THEN 'DC'
WHEN 'Coaches' THEN 'DC'
WHEN 'Student Client' THEN 'ST'
WHEN 'Guest Doctor' THEN 'GDC'
WHEN 'Guest Student' THEN 'GST'
WHEN 'Elite Client' THEN 'DC'
WHEN 'Advanced Client' THEN 'DC'
WHEN 'Maintenance Client' THEN 'DC'
WHEN 'Association Client' THEN 'AD'
WHEN 'MLHC In Training' THEN 'DC'
WHEN 'Employee - Coaching' THEN 'DC'
WHEN 'Employee - Students' THEN 'DC'
WHEN 'Employee - Products' THEN 'DC'
WHEN 'Past Client - Advanced'THEN 'GDC'
WHEN 'Student President' THEN 'ST'
WHEN 'Mentor' THEN 'DC'
WHEN 'MLHC in Process' THEN 'ST'
WHEN 'Employee - MLHC' THEN 'DC'
WHEN 'Guest CA' THEN 'GCA'
WHEN 'Associate of ML Doc' THEN 'AD'
WHEN 'Spouse' THEN 'SP'
WHEN 'CampII' THEN 'DC'
WHEN 'WAC' THEN 'DC'
WHEN '90 Day Client' THEN 'DC'
WHEN 'Website owner' THEN 'DC'
WHEN 'ML Canada' THEN 'DC'
WHEN 'MLMO' THEN 'DC'
WHEN 'Past Client - Elite' THEN 'GDC'
WHEN 'Past Client - Maintenance'THEN 'GDC'
WHEN 'Past Client - Student' THEN 'ST'
WHEN 'Past Client - MLHC' THEN 'GDC'
WHEN 'Leader' THEN 'DC'
WHEN 'Student Club Member' THEN 'GST'
WHEN 'Total Food Makeover' THEN 'DC'
WHEN 'Web Client' THEN 'DC'
WHEN 'Autism Whisperer' THEN 'DC'
WHEN 'Chiropractic Assistant' THEN 'CA'
WHEN 'MLHC Client' THEN 'DC'
WHEN 'Captain' THEN 'DC'
WHEN 'Retreat MLHC' THEN 'DC'
WHEN 'Guest Spouse' THEN 'GSP'
END) as Category
from [3rdi_EventParticipants] as EP
inner join [3rdi_EventSignup] as ES on EP.SignUpId = ES.SignUpId
WHERE EP.EventId = @ItemId AND EP.PlaceStatus IN (0,3,4,8)
and userid in(
select distinct userid from userroles
where roleid not in(19,20,21,22) and roleid not in(1,2, 25, 44))

[edit]
Subject.
Tags.
Unchecked "Ignore HTML..." to make blocks work.
Moved code blocks to only the formatted stuff, rather than whole message.
OriginalGriff[/edit]