SQL Server窗口函数：构建历史字符串

I have a table of longitudinal data that looks like this:

我有一个纵向数据表，如下所示：

where id is the partition variable, period is the time dimension, and val is the observation value.

其中id是分区变量，period是时间维度，val是观察值。

I want to build up a history of val for each panel of id, like this:

我想为每个id面板建立一个val的历史记录，如下所示：

I'm trying to do this with SQL window functions and not a cursor, but the issue I keep running into is the self-referential nature of the hist column definition. It almost seems like I'd have to create one row/column per period. For example, the closest I could come was this:

我正在尝试使用SQL窗口函数而不是游标，但我一直遇到的问题是hist列定义的自引用性质。几乎看起来我必须每个周期创建一行/列。例如，我能来的最接近的是：

IF OBJECT_ID('dbo.my_try', 'U') IS NOT NULL 
    DROP TABLE dbo.my_try; 
GO
SELECT
    id, period, val, 
    CASE
        WHEN (
            period = MIN(period) 
                OVER (PARTITION by id order by period ROWS 
                BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
        ) THEN CAST (val AS VARCHAR(60))
        ELSE NULL           
    END AS hist  
INTO my_try
FROM my_test

SELECT
    id, period, val, 
    CASE
        WHEN (
            period = MIN(period) OVER 
            (PARTITION by id order by period ROWS 
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
        ) THEN hist 
        ELSE (
            CONCAT(
                val, ' | ', LAG(hist, 1) OVER (PARTITION by id order by period)
            )
        )

    END AS hist2
FROM my_try

I would have to spool out the iteration and do a hist3, etc. for it to finally work.

我将不得不假设迭代并执行hist3等，以便最终工作。

Is it possible to accomplish this with SQL window functions, or is cursor the only route?

是否可以使用SQL窗口函数完成此操作，或者光标是唯一的路径？

---

Sample Data

样本数据

Here is some code to generate the original table:

以下是生成原始表的一些代码：

CREATE TABLE my_test (
    id INT,
    period INT,
    val INT 
)
BEGIN
    DECLARE @id INT = 1;
    DECLARE @period INT = 1;

    WHILE @id <= 3
    BEGIN
        SET @period = 1 
        WHILE @period <= 3
        BEGIN
            INSERT INTO my_test VALUES (@id, @period, @period * POWER(10, @id))
            SET @period = @period + 1
        END

        SET @id = @id + 1
    END
END

2 个解决方案

#1

Actually you don't need recursion here. You can leverage STUFF pretty easily. Of course if you are on 2017 you can use string_agg as suggested above. But if you are like me and your company is not the fastest to adopt the latest and greatest you can use this.

其实这里你不需要递归。你可以很容易地利用STUFF。当然，如果您在2017年，您可以使用上面建议的string_agg。但如果你像我一样，你的公司不是最快采用最新最好的你可以使用它。

select t1.id
    , t1.period
    , t1.val
    , STUFF((select ' | ' + convert(varchar(10), val)
            from my_test t2
            where t2.id = t1.id
                and t2.period <= t1.period
            order by t1.period
            FOR XML PATH('')), 1, 3,'')
from my_test t1
order by t1.id
    , t1.period

#2

As discussed in the comments try using recursive query

正如评论中所讨论的那样尝试使用递归查询

with cte as(
select id, [period], val, convert(varchar(max), val) as agg from my_try where [period] = 1
union all
select t.id, t.[period], t.val, CONCAT(c.agg, ' | ', t.val) from my_try t join cte c on c.[period] +1 = t.[period] and c.id = t.id
)
select * from cte order by id, [period]