SQL - 枢轴/ Unpivot?
问题描述:
我在SQL Server 2008中的表格式如下(简化格式):SQL - 枢轴/ Unpivot?
Date Miles Cost
我想得到这样
Previous Week Current Week Variance Miles Cost
结果我怎样才能做到这一点?我是否需要pivot/unpivot或者什么是实现这个目标的最简单和最有效的方法。谢谢。
答
SELECT 'Miles' AS Type, SUM(CASE WHEN date = lastweek THEN miles ELSE 0 END) as lastweek
, SUM(CASE WHEN date = thisweek THEN miles ELSE 0 END) as thisweek
SUM(CASE WHEN date = lastweek THEN miles ELSE 0 END) - SUM(CASE WHEN date = thisweek THEN miles ELSE 0 END) as variance
FROM yourtable
UNION
SELECT 'Cost' AS Type, SUM(CASE WHEN date = lastweek THEN cost ELSE 0 END) as lastweek
, SUM(CASE WHEN date = thisweek THEN cost ELSE 0 END) as thisweek
SUM(CASE WHEN date = lastweek THEN cost ELSE 0 END) - SUM(CASE WHEN date = thisweek THEN cost ELSE 0 END) as variance
FROM yourtable
这是不是100%正确,但语法应该是正确的,应该让你很接近好像你正在寻找
什么不喜欢你需要一个支点为此不惜一切。 – Kermit 2013-02-18 22:16:23
那我该如何实现呢?宁愿有一个更简单的SQL没有多个联合和联合 – devSuper 2013-02-18 22:18:28