Use PIVOT for Multiple records in SQL SERVER

Question

I am using following Query

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
) select isnull( MEMBER_id ,'0' ),
ISNULL ( [MEMBER_PROPERTY] , '''') AS MEMBER_PROPERTY
, ISNULL( [MEMBER_VALUE] ,'''' ) AS MEMBER_VALUE
  from MEMBER_ATTRIBUTES where MEMBER_ID in (86481 )
DECLARE @cols AS NVARCHAR( MAX ),
    @query  AS NVARCHAR ( MAX)
SELECT @cols= stuff((
        SELECT ', ' +QUOTENAME ( MAX( MEMBER_PROPERTY ))
        FROM #MEMBER_ATTRIBUTES
        group by MEMBER_VALUE
        order by MEMBER_VALUE
        FOR XML PATH( '' )), 1 , 2, '');
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'
execute sp_executesql @query;
drop table #MEMBER_ATTRIBUTES

this query return me data in extact format as I want i.e.

But when I tried to run the above query for multiple records by removing where condition from insert. It stops working and throw me error:

The column 'My main interests' was specified multiple times for 'p'.

as per my understanding the above query tries to add the "My main interests" which is not possible, that's why i get the above error, now I am not getting how can I resolve this error. I have used the above script from an answer got from my old question i.e. My Old Question

Please help me how can I resolve this.

UPDATE::

Answer 1

Add distinct keyword while fetching unique columns as:

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, '');

Demo