PIVOT 未按预期执行
it1352
帮助1人
问题说明
抱歉之前有一个不清楚的问题；希望我能重新开始……
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
正确答案
#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.
The flaw was that the input table should have had 1, 1, 2, 2, 3, 3 for the entityid rows, respectively.
M
这篇好文章是转载于：学新通技术网
