PIVOT 未按预期执行

Question

抱歉之前的问题不清楚；希望我可以重新开始...

我有这个数据：

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

有人能告诉我为什么其他行没有被产生吗？我期待看到：

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

Answer 1

答案是这样的：

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。

中号