在 sql server 2008 R2 中使用 PIVOT 时获取空数据 [英] Getting Null data while using PIVOT in sql server 2008 R2

问题描述

我在 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;

这篇关于在 sql server 2008 R2 中使用 PIVOT 时获取空数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！