創建基於樞軸的變數

我有如下所示的 DDL，它返回如下數據：

GP17    GP16
0       103
90      0
375     0

我想以某種方式旋轉數據，使其僅返回一行，如下所示：

Jan 17 ---- Jan 16 ---- Feb 17 ---- Feb 16 ---- Mar 17 ---- Mar 16
0            103         90          0           375         0   

現在這與下面傳入的日期範圍有關，例如，如果傳入的日期範圍是，20170401 20170630那麼我希望日期為 4 月 17 日、4 月 16 日、5 月 17 日、5 月 16 日、6 月 17 日、6 月 16 日

如何使用 SQL Server 2008 R2 和下面的 DDL 來實現這一點？

Declare @Test Table
(
   dname varchar(100)
   ,tr float
   ,dc float
   ,sdate date
)


Insert Into @Test (dname, tr, dc, sdate) Values
('Green', 100.00, 10.00, '20170101'), ('Green', 200.00, 15.00, '20170201'), ('Green', 200.00, 10.00, '20170215')
,('Green', 10.00, 0.00, '20160101'), ('Green', 15.00, 10.00, '20160130'), ('Green', 110.00, 22.00, '20160101')

Declare @startdate date = '20170101', @enddate date = '20170331'

SELECT 
ROUND(SUM(ISNULL(tr17,0))-SUM(ISNULL(DC17,0)),0) As GP17,
ROUND(SUM(ISNULL(tr16,0))-SUM(ISNULL(DC16,0)),0) As GP16
FROM
(
SELECT
ZT1.dname,
SUM(ISNULL(ZT1.tr,0)) AS tr17,
0 As DC17,
0 As DC16,
0 AS tr16,
sdate As activityDate
FROM @Test AS ZT1
WHERE ZT1.sdate >= @startdate
AND ZT1.sdate <= @enddate
GROUP BY ZT1.dname, ZT1.sdate
UNION ALL
Select
t1.dname,
0 As tr17,
SUM(ISNULL(t1.dc,0)) As DC17,
0 As DC16,
0 AS tr16,
sdate as activityDate
FROM @Test t1
WHERE t1.sdate >= @startdate
AND t1.sdate <= @enddate
Group By t1.dname, t1.sdate
UNION ALL
SELECT
ZT1.dname,
0 As tr17,
0 As DC17,
0 As DC16,
SUM(ISNULL(ZT1.tr,0)) AS tr16,
sdate As activityDate
FROM @Test AS ZT1
WHERE ZT1.sdate >= DateAdd(yy, -1, @startdate)
AND ZT1.sdate <= DateAdd(yy, -1, @enddate)
GROUP BY ZT1.dname, ZT1.sdate
UNION ALL
Select
t1.dname,
0 As tr17,
0 As DC17,
SUM(ISNULL(t1.dc,0)) As DC16,
0 AS tr16,
sdate as activityDate
FROM @Test t1
WHERE t1.sdate >= DateAdd(yy, -1, @startdate)
AND t1.sdate <= DateAdd(yy, -1, @enddate)
Group By t1.dname, t1.sdate
) AS A
GROUP BY YEAR(activityDate), MONTH(activityDate)

PIVOT解決方案需要眾所周知的列數。

如果您正在尋找涵蓋任何日期的答案，我建議您搜尋“動態樞軸”

;WITH data AS
(
    SELECT  dname, 
            tr - dc as result,
            LEFT(DATENAME(MONTH, sdate), 3) + '-' + RIGHT(CAST(YEAR(sdate) AS varchar(10)), 2) as DateName
    FROM    @Test
)
SELECT dname, [Jan-16],[Jan-17],[Feb-16],[Feb-17]
FROM   (SELECT * FROM data) src
PIVOT  (SUM(result) FOR DateName IN([Jan-16],[Jan-17],[Feb-16],[Feb-17])) pvt;

GO

名稱 | 16-16 | 17 月 | 2 月 16 日 | 2月17日
:---- | -----: | -----: | -----: | -----:
綠色 | 103 | 90 | *空*| 375

dbfiddle在這裡

引用自：https://dba.stackexchange.com/questions/181624