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屋！