Rows to columns in sql server

As usual, a frequent article in sql server.

 I am trying to covert the rows to columns in sql server.

Problem description: Am having a table with list of participants and number of programs.

I need to give a binary value based on the participants for a specific program. Lets see how to arrive this.

Declared and inserting data into the table @ Attendees 

declare @Attendees table
( FunctionID integer,
DetailID integer,
ParticipantID integer,
StatusID integer
)
insert into @Attendees
select 1, 1, 123, 6 union all
select 1, 2, 124, 6 union all
select 1, 2, 125, 7
select * from @Attendees

Declared and inserting data into the table @ Status

declare @Status table
( StatusId integer,
Description varchar(10)
)
insert into @status
select 6, 'Invited' union all
select 7, 'Accepted'
select * from @status

Getting the output with duplicates.

select a.FunctionID,a.DetailID,--b.Description
case
when b.Description='Invited' then 1 else 0
end as invited,
case
when b.Description='Accepted' then 1 else 0
end as Accepted
from @Attendees a inner join
@status b on a.statusID=b.statusID

Getting the exact required output.

select a.FunctionID,a.DetailID,--b.Description
sum(case
when b.Description='Invited' then 1 else 0
end) as invited,
sum(case
when b.Description='Accepted' then 1 else 0
end) as Accepted
from @Attendees a inner join
@status b on a.statusID=b.statusID
group by a.FunctionID,a.detailid

Thanks and Regards,

Venkatesan Prabu .J