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.

Out Put Like this

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::

Here is data and table structure

PLease post some data from table #MEMBER_ATTRIBUTES and expected result when multiple MEMBER_ID's are to be considered to help you fast.. — Deepshikha, Apr 16 '14 at 10:53
@Deepshikha i have added screenshot for table structure and data — Ram Singh, Apr 16 '14 at 10:55
try adding a distinct: @cols= stuff(( SELECT DISTINCT ', ' +QUOTENAME ( MAX( MEMBER_PROPERTY )) — Leo, Apr 16 '14 at 11:12

score 1 · Accepted Answer · answered Apr 16 '14 at 11:26

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

Taryn · Answer 2 · 2014-04-16T12:13:50.863

To get the column names dynamically you will want to use the following:

SET @cols = STUFF((SELECT ',' + QUOTENAME(MEMBER_PROPERTY) 
            FROM #MEMBER_ATTRIBUTES
            group by MEMBER_PROPERTY, MEMBER_VALUE
            order by MEMBER_VALUE
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

This will select the distinct MEMBER_PROPERTY values and convert them to a comma separated list.

Use PIVOT for Multiple records in SQL SERVER

2 Answers2

Linked