This is the abstract idea which i want to do by my first select of first line, i am presenting here, but i am unable to do that correct. here category[0], category[2]..etc representing the category columns values...i know this kind of syntax not work, but i want to do something like this.
SELECT category[0], category[1], category[2], category[3], category[4], category[5]
FROM(
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'
---....more categories here, i just removed a few
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))
(My Below Query)
PIVOT(sum(First_Name+Last_Name)) FOR Category (category[0], category[1], category[2], category[3], category[4], category[5])
Group by (SignUpID)
