在 sql server 2008 R2 中使用 PIVOT 时获取 Null 数据

2022-01-22 00:00:00 pivot sql sql-server

我在 PIVOT 中使用以下查询:

I am using the following query with 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

此查询为['我的主要兴趣']、旅行、住宿、奖品抽奖"列返回 Null，我不明白为什么，因为该表包含每条记录的这些列的数据..

This query returns Null for "['My main interests'], traveling,accommodation, prizedraw" columns, i am not getting why, as the table has data for these columns for each record..

请帮助我如何获取数据.

Please help me how can I get data.

更新 ::

推荐答案

我想这会对你有所帮助.我们应该为空格列使用 QuoteName 函数.

I think this will help you.we should use QuoteName function for space column.

    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;

相关文章