Need help in Pivoting the Data

declare @Perspectives table (name varchar(10), data varchar(50))

insert into @Perspectives select 'AAA', 'Question1'
insert into @Perspectives select 'BBB', 'Question2'
insert into @Perspectives select 'AAA', 'Question3'
insert into @Perspectives select 'AAA', 'Question4'
insert into @Perspectives select 'BBB', 'Question5'

select AAA, BBB
from (select row_number() over( partition by name order by data) as rowid, 
             name, data from @Perspectives)p
pivot (max(data) for name in ([AAA],[BBB])) as pvt

or if you need a more dynamic solution that doesn't require all the values of name to be known ahead of run-time (needs a table, rather than table variable)

create table Perspectives (name varchar(10), data varchar(50))

insert into Perspectives select 'AAA', 'Question1'
insert into Perspectives select 'BBB', 'Question2'
insert into Perspectives select 'AAA', 'Question3'
insert into Perspectives select 'AAA', 'Question4'
insert into Perspectives select 'BBB', 'Question5'

select AAA, BBB
from (select row_number() over( partition by name order by data) as rowid, 
             name, data from Perspectives)p
pivot (max(data) for name in ([AAA],[BBB])) as pvt



DECLARE @cols NVARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ',[' + name + ']','[' + name + ']')
FROM    (select distinct Name from Perspectives) a

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT '+
@cols +'
FROM
    (select row_number()over(partition by name order by (select null)) as rowid, 
            name, data from Perspectives)p
PIVOT
(
MAX(data)
FOR Name IN
( '+
@cols +' )
) AS pvt;'

execute (@query)

drop table Perspectives