sql-server - 如果当前列在数据透视表中为空，则获取上一列值

Question

我有这样的数据：

Count | Status | Date
1     | S1     | Jan 2020
5     | S1     | Feb 2020
10    | S1     | Mar 2020
15    | S1     | Apr 2020
1     | S2     | Jan 2020
                          <- feb 2020 is missing for status2
10    | S2     | Mar 2020
                          <- apr 2020 is missing for status2

它基本上是每个月每个状态的累积数据，我需要将日期作为列名，然后按状态对它们进行分组，因此我需要对其进行透视。

当我旋转给定的数据时，它看起来像这样：

Status | Jan 2020 | Feb 2020 | Mar 2020 | Apr 2020
S1     | 1        | 5        | 10       | 15
S2     | 1        | NULL     | 10       | NULL

所以我想要的是当该列为 NULL 时它将获取前一列的数据，因此在此示例中Feb 2020，状态的数据S2应该是1因为它是具有值的前一列，也Apr 2020应该是10因为它是前一列具有值的列。

像这样的东西：

Status | Jan 2020 | Feb 2020 | Mar 2020 | Apr 2020
S1     | 1        | 5        | 10       | 15
S2     | 1        | 1 <-     | 10       | 10 <-

当您像这样旋转数据时，如何实现这一目标？

这是我到目前为止的快速示例：

--Set the end of the month when getting the @columns variable
DECLARE @toDate DATETIME = DATEADD(MONTH, 1, GETDATE()); 


-- I have a function that gets the 1st month of the year until the month set by @toDate variable to set them as columns in the pivot so it would look like this:
DECLARE @columns NVARCHAR(MAX)= '[Jan 2020],[Feb 2020],[Mar 2020],[Apr 2020]';


SELECT * FROM   
(
    SELECT DISTINCT 
        FORMAT(Date, 'MMM yyyy') AS [Date],
        SUM(COUNT(*)) OVER (PARTITION BY [Status] ORDER BY [Date]) AS [Count],
        [Status]
    FROM Table1
    WHERE Date <= @toDate
    GROUP BY [Date], FORMAT([Date], 'MMM yyyy'), [Status]
) t 
PIVOT(
    MAX(Count)
    for [Date] IN (@columns)
) AS pivot_table;   

标签： sql-server