compile sql server online

Language: Layout:

--Title of this code
--Batches are separated by 'go'

select @@version as 'sql server 2008'
CREATE Table   #MEMBER_ATTRIBUTES   (
MEMBER_ID int,
MEMBER_PROPERTY varchar( 500 ),
MEMBER_VALUE varchar( 500 )
)

insert INTO #MEMBER_ATTRIBUTES ( MEMBER_ID ,MEMBER_PROPERTY
, MEMBER_VALUE
) values 
(662710, 'My Main Interests', 'Diving'),
(662710, 'I''m most likely to be travelling', 'With a Partner'),
(662710, 'Preffered Accomodation Type', 'Dive Lodge'),
(662710, 'I would like to enter the prize draw', 'Yes'),
(707411, 'My Main Interests', 'Diving, Other watersports'),
(707411, 'I''m most likely to be travelling', 'With friends'),
(707411, 'Preffered Accomodation Type', 'Rented Villa'),
(707411, 'I would like to enter the prize draw', 'Yes')

DECLARE @cols AS NVARCHAR( MAX ),
    @query  AS NVARCHAR ( MAX)
SELECT @cols= stuff((
        SELECT distinct ', ' +QUOTENAME ( MAX( MEMBER_PROPERTY ))
        FROM #MEMBER_ATTRIBUTES
        group by MEMBER_VALUE
        --order by MEMBER_VALUE
        FOR XML PATH( '' )), 1 , 2, '');
--select @cols;

SET @query = 'SELECT MEMBER_ID, ' + @cols + '
        from
         (
               SELECT MEMBER_ID,MEMBER_VALUE,MEMBER_PROPERTY FROM #MEMBER_ATTRIBUTES
        ) x
        pivot
        (
        MAX(MEMBER_VALUE)
            for x.MEMBER_PROPERTY in (' + @cols + ')
        ) p'
--select @query;
execute sp_executesql @query;
drop table #MEMBER_ATTRIBUTES

View schema
Execution time: 0.02 sec, rows selected: 5, rows affected: 8, absolute service time: 0.14 sec