Ram Singh Ram Singh - 1 month ago 8
SQL Question

Use PIVOT for Multiple records in SQL SERVER

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

Answer

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