对SQL SERVER中的多个记录使用PIVOT [英] Use PIVOT for Multiple records in SQL SERVER

问题描述

我正在使用以下查询

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.

但是当我尝试通过从插入中删除where条件来对多个记录运行上述查询时.它停止工作并抛出错误:

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:

为"p"多次指定了我的主要兴趣"列.

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.

更新::

推荐答案

添加distinct关键字，同时将唯一列提取为:

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

演示

这篇关于对SQL SERVER中的多个记录使用PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！