在server 2008 R2中使用PIVOT获取空数据

Question

我正在使用以下对PIVOT的查询：

SELECT *
  FROM (
     SELECT 
         MEMBER_PROPERTY as [MEMBER_PROPERTY],MEMBER_VALUE as MEMBER_VALUE, 
         MEMBER_ID as MEMBER_ID 
         FROM MEMBER_ATTRIBUTES) as s
   PIVOT
(
     max(MEMBER_PROPERTY)
     FOR [MEMBER_VALUE] IN (['My main interests'], traveling,accommodation, prizedraw)
)AS test

此查询返回“我的主要兴趣爱好”、“旅行、住宿、原始”列的空值，我不知道为什么，因为表中每个记录都有这些列的数据。

请帮助我如何获得数据。

最新情况：

​

​

Answer 1

我认为这将有助于you.we对空间列使用QuoteName函数。

    CREATE Table  #MEMBER_ATTRIBUTES  (
MA_ID int,
MEMBER_ID int,
MEMBER_PROPERTY varchar(100),
MEMBER_VALUE varchar(100)
)

insert INTO #MEMBER_ATTRIBUTES values(1,1,'My Main Interests','traveling')
insert INTO #MEMBER_ATTRIBUTES values(2,1,'I m Most Likly to be travelling','With a parents')
insert INTO #MEMBER_ATTRIBUTES values(3,2,'My Main Interests','prizedraw')
insert INTO #MEMBER_ATTRIBUTES values(4,2,'I m Most Likly to be travelling','With my wife')


DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

SELECT @cols= stuff((
            SELECT ', ' +QUOTENAME(MAX(MEMBER_PROPERTY))
            FROM #MEMBER_ATTRIBUTES
            group by MEMBER_PROPERTY
            order by MEMBER_PROPERTY
            FOR XML PATH('')), 1, 2, '');

SET @query = 'SELECT MA_ID,MEMBER_ID, ' + @cols + '
            from 
             (
                   SELECT MA_ID,MEMBER_PROPERTY as [MEMBER_PROPERTY],MEMBER_VALUE as MEMBER_VALUE, 
     MEMBER_ID as MEMBER_ID  FROM #MEMBER_ATTRIBUTES
            ) x
            pivot 
            (
            MAX(MEMBER_VALUE)
                for x.MEMBER_PROPERTY in (' + @cols + ')
            ) p'

execute sp_executesql @query;