Interview Question:- I have one table with following data in sql server CREATE TABLE [dbo].[T_COL](
    [COL1] [varchar](10) NULL,
    [COL2] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'A', 1)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'A', 2)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'A', 3)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'B', 1)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'B', 2)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'B', 3)


The desired output should look like this:-

col1     col2
A         1
A         1,2
A         1,2,3
B         1
B         1,2
B         1,2,3
You need a rolling string aggregate function. STRING_AGG doesn't allow OVER() clause, so a solution is to use a correlated subquery with FOR XML to generate the list.

SELECT
    T.COL1,
    RollingAggregate = STUFF (
        (
            SELECT
                ',' + CONVERT(VARCHAR(10), N.COL2)
            FROM
                [dbo].[T_COL] AS N
            WHERE
                N.COL1 = T.COL1 AND     -- Same letter
                N.COL2 &lt;= T.COL2        -- Equal or lower number
            ORDER BY
                N.COL2
            FOR XML
                PATH('')
        ),
        1, 1, '') -- Remove leading comma
FROM
    [dbo].[T_COL] AS T
ORDER BY
    T.Col1,
    T.Col2


Result:

COL1    RollingAggregate
A       1
A       1,2
A       1,2,3
B       1
B       1,2
B       1,2,3