Is it possible to use dynamic columns multiple times in a select?

Question

I have some complex string replacement and I'm only permitted read-only access.

I'd like to have several columns where each is dependent on the previous column so I can see if my substring and charindex functions are correct.

Basically this:

DECLARE @myvar NVARCHAR(100) = 'ThisIsMyTest'

SELECT REPLACE(@myvar, 'My', 'Our') AS Part1
    ,Part1 + ' added to the end'

Where I would want the output of the second column to be:

ThisIsOurTest added to the end

This is an extremely simplified version of what I'm trying to accomplish, so I'm looking for the concept. I don't need this answer:

SELECT REPLACE(@myvar, 'My', 'Our') as Part1, REPLACE(@myvar, 'My', 'Our') + ' added to the end'

as that's what I'm doing now.

Answer 1

In SQL Server, one option uses lateral joins. They are handy in this situation, because the "next" join has access to the dataset generated by the previous one(s). Basically, you can put each intermediate computation in a separate (scalar) subquery.

Starting from your simplified example, that would be:

select t1.part1, t2.part2
from (values (replace(@myvar, 'My', 'Our'))) t1(part1)
cross apply (values (t1.part1 + ' added to the end')) t2(part2)