时间戳

首页 > 解决方案 > 带有 varchar 数据类型的 SQL PIVOT 返回 null

sql - 带有 varchar 数据类型的 SQL PIVOT 返回 null

问题描述

我正在尝试对 sql 表中的数据进行透视，以便每个记录 id 只得到一行。但是，我正在运行的代码不断返回空值作为我的最终结果。

我的表看起来像：

"Sequence"  "University Issuing Body"**    
"1" "College1"    
"1" "College2"
"2" "College1"    
"2" "College1"    
"2" "College2"

我需要的表格如下所示：

"Sequence"  "University Issuing Body1"  "University Issuing Body2"  "University Issuing Body3"    
"1" "College1"  "College2"  "NULL"
"2" "College1"  "College1"  "College2"

我正在运行的代码如下：

select
     Sequence]
    ,[University Issuing Body_1]
    ,[University Issuing Body_2]
    ,[University Issuing Body_3]

from (
    select
         [Sequence]
        ,[University Issuing Body]
        ,'University Issuing Body' + cast(row_number() over(partition by [Sequence] order by [University Issuing Body]) as varchar(12)) as hdg
    from [AB_DCU_IP_2018].[dbo].[PR_Q_Joined]
    ) d
    pivot(
        max([University Issuing Body])
        for hdg in ([University Issuing Body_1], [University Issuing Body_2], [University Issuing Body_3])
        )pvt

但是，当我运行上面的代码时，我的所有值都显示为 null，我得到以下结果：

"Sequence"  "University Issuing Body_1" "University Issuing Body_2" "University Issuing Body_3"

"1" "NULL"  "NULL"  "NULL"

"2" "NULL"  "NULL"  "NULL"

任何帮助都将不胜感激，因为我一直在网上寻找解决方案，但似乎无法找到我哪里出错了。

提前致谢。

标签： sqlsql-serverpivot

解决方案

您正在使用 hdg 格式构建中间表University Issuing Body<n>。但是您随后会提取 hdg 具有 format 的值University Issuing Body_<n>。不出所料，这不匹配任何东西 - 所以你得到 NULL。

只需在 hdg 的构造中添加下划线：

select
 [Sequence]
,[University Issuing Body_1]
,[University Issuing Body_2]
,[University Issuing Body_3]
from (
    select
        [Sequence]
        ,[University Issuing Body]
        ,'University Issuing Body_' + cast(row_number() over(partition by [Sequence] order by [University Issuing Body]) as varchar(12)) as hdg
    from [AB_DCU_IP_2018].[dbo].[PR_Q_Joined]
    ) d
    pivot(
        max([University Issuing Body])
        for hdg in ([University Issuing Body_1], [University Issuing Body_2], [University Issuing Body_3])
        ) pvt

推荐阅读