CODEWITHSUNDEEP
sqlsql-serverstring-concatenation
Joe Bank
Joe Bank

Reputation: 663

Concatenating strings based on previous row values

Consider the following input:

ID
--------
33
272
317
318

I need to somehow get the following result:

Result
--------
/33
/33/272
/33/272/317
/33/272/317/318

How can I achieve this with a single SELECT statement?

Upvotes: 1

Views: 1134

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Using CROSS APPLY and FOR XML PATH():

;WITH Cte AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM tbl
)
SELECT *
FROM Cte c
CROSS APPLY(
    SELECT '/' + CONVERT(VARCHAR(10), ID)
    FROM Cte
    WHERE rn <= c.rn
    FOR XML PATH('')
)x(s)

Upvotes: 3

Related Questions