SQL Server - Concatenate previous 5 rows

Question

I've got sql code which creates an average of the previous 5 rows within column1 after partitioning and ordering:

CAST (AVG(col1) OVER (Partition by col2 ORDER BY col3 ASC ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS DECIMAL(3,2)) AS col1avg

I want to do a similar thing, but instead of averaging the 5 values, I want to create a string with the 5 values separated by a comma. I've tried multiple ideas with no success. Any help will be greatly appreciated.

Answer 1

The simplest method in SQL Server 2012+ is to use CONCAT() and LAG():

select CONCAT(LAG(col1, 1) OVER (PARTITION BY col2 ORDER BY col3), ',',
              LAG(col1, 2) OVER (PARTITION BY col2 ORDER BY col3), ',',
              LAG(col1, 3) OVER (PARTITION BY col2 ORDER BY col3), ',',
              LAG(col1, 4) OVER (PARTITION BY col2 ORDER BY col3), ','
              LAG(col1, 5) OVER (PARTITION BY col2 ORDER BY col3)
             )

If you need to handle NULL values, this is a bit trickier. I would recommend using spaces, trim, and replace:

select REPLACE(RTRIM(CONCAT(LAG(col1, 1, '') OVER (PARTITION BY col2 ORDER BY col3), ' ',
                            LAG(col1, 2, '') OVER (PARTITION BY col2 ORDER BY col3), ' ',
                            LAG(col1, 3, '') OVER (PARTITION BY col2 ORDER BY col3), ' ',
                            LAG(col1, 4, '') OVER (PARTITION BY col2 ORDER BY col3), ' ',
                            LAG(col1, 5, '') OVER (PARTITION BY col2 ORDER BY col3)
                           )
                    ), ' ', ','
              )