PIVOT 未按预期执行 [英] PIVOT not performing as expected

问题描述

抱歉之前有一个不清楚的问题；希望我能重新开始……

Sorry for an unclear question previously; hopefully I can start again...

我有这些数据:

entityid    name                 stringvalue
----------- -------------------- --------------------
1           ShortDescription     Coal
1           LongDescription      BlackCoal
1           ShortDescription     Gold
1           LongDescription      WhiteGold
1           ShortDescription     Steel
1           LongDescription      StainlessSteel

这个查询:

select *
from
(
    select entityid, name, stringvalue as stringvalue
    from mytable
) as d
pivot
(
    min([stringvalue])
    for [name] in ([ShortDescription],[LongDescription])
)
as p

产生这个输出:

entityid ShortDescription LongDescription
-------- ---------------- ---------------
1        Coal             BlackCoal

有人能告诉我为什么没有生成其他行吗?我期待看到:

Could someone tell me why the other rows are not being produced, please? I was expecting to see:

entityid ShortDescription LongDescription
-------- ---------------- ---------------
1        Coal             BlackCoal
1        Gold             WhiteGold
1        Steel            StainlessSteel

推荐答案

答案竟然是这样的:

select *
from
(
    select entityid, [name], stringvalue as stringvalue
    from mytable
) as d
pivot
(
    min(stringvalue)
    for [name] in ([ShortDescription],[LongDescription])
)
as p

:)

缺陷在于输入表的 entityid 行应该分别为 1、1、2、2、3、3.

The flaw was that the input table should have had 1, 1, 2, 2, 3, 3 for the entityid rows, respectively.

M

这篇关于PIVOT 未按预期执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！