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

素材狗 2022-01-01 21:32:30 文章分类：数据库问题点击数：1次

文章标签

Getting Null data while using PIVOT in sql server 2008 R2(在 sql server 2008 R2 中使用 PIVOT 时获取 Null 数据)

本文介绍了在 sql server 2008 R2 中使用 PIVOT 时获取 Null 数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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