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