>>You can have a view in SQL Server that queries tables. And obviously the view can have CTEs...
>
>That is correct. However, I would like it to be done on the native table.

OK, if I can ask - is there a reason you want to do it on a table instead of a view?
Maybe I'm confused - but when you mentioned a CTE, I figured you needed to shape/aggregate the data beyond what the native tables contained.