如何从 sql server 中的数据透视查询仅返回一个结果集?
我有以下 T-SQL 数据透视查询。
SELECT AccountNumber, EventID,
CreateDate, [ITEMBOOK] AS ITEMBOOK,
[POSTER] AS POSTER
FROM
(SELECT ProductID, Quantity, EventID,AccountNumber,CreateDate
FROM #tmpStartupItems) ps
PIVOT
(
SUM (Quantity)
FOR ProductID IN
( [ITEMBOOK], [POSTER])
) AS pvt
当我运行它时,它返回两个结果集...有没有办法限制它只返回旋转的结果集?
赛斯
I have the following T-SQL Pivot query.
SELECT AccountNumber, EventID,
CreateDate, [ITEMBOOK] AS ITEMBOOK,
[POSTER] AS POSTER
FROM
(SELECT ProductID, Quantity, EventID,AccountNumber,CreateDate
FROM #tmpStartupItems) ps
PIVOT
(
SUM (Quantity)
FOR ProductID IN
( [ITEMBOOK], [POSTER])
) AS pvt
When i run this it is returning both of the resultsets...is there a way to limit it to return just the pivoted result set?
Seth
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
什么“两个结果集”? 您应该只从单个 SELECT 语句获取单个结果集,无论是否为 PIVOT。 子查询中的 SELECT 不是第二个 SELECT 语句,它是一个表表达式(不同的东西),它应该只将其数据返回到更大的调用 SELECT 语句。
以下是 BOL 数据透视示例:
当我对 AdventureWorks 数据库执行此操作时,我只得到一个结果集,正如预期的那样。
如果您获得多个结果集,则要么 1) 该查询是一个更大的存储过程的一部分,该存储过程正在执行另一个查询并返回其他结果集(因此更改存储过程),或者 2) 您混淆了其他内容(例如 PRINT 语句)作为结果集,或者 3)您的 SQL Server 或访问工具存在严重错误。
What "both resultsets"? You should only get a single result set from a single SELECT statement, PIVOT or not. And the SELECT in the subquery is not a second SELECT statement, it's a table expression (different thing), it should only return it's data to the larger calling SELECT statement.
Here is the BOL pivot example:
When I execute this agains the AdventureWorks database, I only get one result set, as expected.
If you are getting more then one resultset then either 1) this query is part of a larger stoered procedure that is executing another query and returning that other result set also (so change the stored procedure), OR 2) you are confusing something else (like PRINT statements) with being a result set, OR 3) there is something seriously wrong with your SQL Server or access tools.