是否可以在 SQL PIVOT 中使用动态创建的值？
我有以下功能： -
ALTER FUNCTION fncTest ()
RETURNS TABLE
AS
RETURN
(SELECT *
FROM (SELECT TOP 100 PERCENT sOrderType,
SUM(iQty) AS iOrdQty,
( YEAR(dReqd) * 100 ) + DATEPART(Week, dReqd) AS iWkNo
FROM tblOrderBook
GROUP BY sOrderType,
dOrdered) AS tblTemp PIVOT(SUM(Qty) FOR iWkNo IN
([201118], [201119], [201120], [201121], [201122])) AS pvtTemp)
这为我提供了一个数据透视表，显示 2011 年第 18-22 周的订单数量。
是否可以用基于 GETDATE() 的动态日期替换硬编码的周。
ie:
- 将
[201118]替换为(YEAR(GETDATE()+7)*100)+DATEPART(week,GETDATE()+7) - 替换
[200119]< /code> 为(YEAR(GETDATE()+14)*100)+DATEPART(week,GETDATE()+14) - 将
[200120]替换为( YEAR(GETDATE()+21)*100)+DATEPART(week,GETDATE()+21)
等等...
谢谢。
I have the following function: -
ALTER FUNCTION fncTest ()
RETURNS TABLE
AS
RETURN
(SELECT *
FROM (SELECT TOP 100 PERCENT sOrderType,
SUM(iQty) AS iOrdQty,
( YEAR(dReqd) * 100 ) + DATEPART(Week, dReqd) AS iWkNo
FROM tblOrderBook
GROUP BY sOrderType,
dOrdered) AS tblTemp PIVOT(SUM(Qty) FOR iWkNo IN
([201118], [201119], [201120], [201121], [201122])) AS pvtTemp)
This gives me a pivoted table showing qtys of orders for weeks 18-22 of 2011.
Is it possible to replace the hard-coded weeks with dynamic dates based on GETDATE().
ie:
- replace
[201118]with(YEAR(GETDATE()+7)*100)+DATEPART(week,GETDATE()+7) - replace
[200119]with(YEAR(GETDATE()+14)*100)+DATEPART(week,GETDATE()+14) - replace
[200120]with(YEAR(GETDATE()+21)*100)+DATEPART(week,GETDATE()+21)
etc...
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不是你想做的那样。执行此操作的唯一方法是动态 SQL，并且不能在函数中使用动态 SQL。
但是您可以使用固定列名称，例如
[1],[2],[3]等来表示相对于当前日期的周数。Not as you want to do it. The only way of doing this is dynamic SQL and you can't use dynamic SQL in a function.
But you could use fixed column names such as
[1],[2],[3]etc to represent the week number relative to the current date.