SQL 按日期统计后列转行
以下是带批注的 PIVOT 语法。
SELECT <非透视的列>,
[第一个透视的列] AS <列名称>,
[第二个透视的列] AS <列名称>,
...
[最后一个透视的列] AS <列名称>,
FROM
(<生成数据的 SELECT 查询>)
AS <源查询的别名>
PIVOT
(
<聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
IN ( [第一个透视的列], [第二个透视的列],
... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;
示例1：
CREATE TABLE #Product
(
DaysToManufacture INT ,
StandardCost DECIMAL(12, 4)
);
GO
INSERT INTO #Product
VALUES ( 0, 5.0885 );
INSERT INTO #Product
VALUES ( 0, 5.0885 );
INSERT INTO #Product
VALUES ( 1, 223.88 );
INSERT INTO #Product
VALUES ( 1, 223.88 );
INSERT INTO #Product
VALUES ( 2, 359.1082 );
INSERT INTO #Product
VALUES ( 4, 949.4105 );
SELECT DaysToManufacture ,
AVG(StandardCost) AS AverageCost
FROM #Product
GROUP BY DaysToManufacture;
查询结果：
DaysToManufacture AverageCost
0 5.088500
1 223.880000
2 359.108200
4 949.410500
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days ,
[0] ,
[1] ,
[2] ,
[3] ,
[4]
FROM ( SELECT DaysToManufacture ,
StandardCost
FROM #Product
) AS SourceTable PIVOT
( AVG(StandardCost) FOR DaysToManufacture IN ( [0], [1], [2], [3], [4] ) ) AS PivotTable;
转置结果
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.088500 223.880000 359.108200 NULL 949.410500
现实示例：
1、先统计结果
SELECT
datepart(month,ibb.CreateTime) AS [月份],datepart(day,ibb.CreateTime) as[日期],COUNT(DISTINCT ibb.BillNo) as [单据量],SUM(ibbd.Quantity) as [件数]
FROM InBoundBill ibb (NOLOCK)
JOIN InBoundBillDetail ibbd (NOLOCK)
ON IBB.BillId = IBBD.BillId
WHERE ibb.TypeId = 30
AND ibb.CreateTime > '2012-01-01' AND ibb.CreateTime < '2012-12-21'
AND ibb.WarehouseId IN (1)
GROUP BY datepart(month,ibb.CreateTime),datepart(day,ibb.CreateTime)
ORDER BY datepart(month,ibb.CreateTime),datepart(day,ibb.CreateTime)
| 月份 | 日期 | 单据量 | 件数 |
| 1 | 1 | 492 | 670 |
| 2 | 399 | 550 | |
| 3 | 487 | 698 | |
| 4 | 487 | 672 | |
| 5 | 507 | 662 | |
| 6 | 605 | 804 | |
| 7 | 666 | 943 | |
| 8 | 439 | 591 | |
| 9 | 599 | 832 | |
| 10 | 530 | 690 | |
| 11 | 554 | 741 | |
| 12 | 631 | 881 | |
| 13 | 574 | 771 | |
| 14 | 577 | 796 | |
| 15 | 409 | 576 | |
| 16 | 420 | 562 | |
| 17 | 445 | 590 | |
| 18 | 667 | 901 | |
| 19 | 406 | 545 | |
| 20 | 427 | 624 | |
| 21 | 320 | 465 | |
| 25 | 3 | 3 | |
| 26 | 241 | 412 | |
| 27 | 214 | 318 | |
| 28 | 180 | 251 | |
| 29 | 342 | 483 | |
| 30 | 162 | 240 | |
| 31 | 360 | 480 |
2、列转行
;WITH tmp AS(
SELECT DATEPART(month , ibb.CreateTime) AS [month] , DATEPART(day , ibb.CreateTime) AS [day] ,
LTRIM(ISNULL(COUNT(DISTINCT ibb.BillNo) , 0))+'/'+LTRIM(ISNULL(SUM(ibbd.Quantity) , 0)) AS [Quantity]
FROM InBoundBill ibb (NOLOCK)
JOIN InBoundBillDetail ibbd (NOLOCK)
ON IBB.BillId = IBBD.BillId
WHERE ibb.TypeId = 30
AND ibb.CreateTime > '2012-01-01'
AND ibb.CreateTime < '2012-12-21'
AND ibb.WarehouseId IN (1)
GROUP BY DATEPART(month , ibb.CreateTime) , DATEPART(day , ibb.CreateTime)
)
SELECT *
FROM tmp PIVOT ( MAX([Quantity]) FOR [day] IN ([1] , [2] , [3] , [4] , [5] , [6] , [7] , [8] , [9] , [10] , [11] , [12] , [13] , [14] , [15] , [16] , [17] ,
[18] , [19] , [20] , [21] , [22] , [23] , [24] , [25] , [26] , [27] , [28] , [29] , [30] , [31]) ) piv
ORDER BY [month]
| 月 日 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 |
| 1 | 492/670 | 399/550 | 487/698 | 487/672 | 507/662 | 605/804 | 666/943 | 439/591 | 599/832 | 530/690 | 554/741 | 631/881 | 574/771 | 577/796 | 409/576 | 420/562 | 445/590 | 667/901 | 406/545 | 427/624 | 320/465 | 0 | 0 | 0 | 3/3 | 241/412 | 214/318 | 180/251 | 342/483 | 162/240 | 360/480 |
