I don't really see the need for the overhead and potential issues of reading the sys.all_columns view.

;WITH
cteTally10 AS (
  SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))...

SELECT FieldName,
  MAX(CASE WHEN ID = 2 THEN RecordCount END) AS OldCount,
  MAX(CASE WHEN ID = 1 THEN RecordCount END) AS NewCount,
  MAX(CASE WHEN...

Prob not "the best way", but I think it works:

select ssalesid, salesname, salesamount_split, rank
from (
  select 1 as ssalesid, 'cust1' as salesname, cast(101.50...

Are they covering indexes?  Just having a nonclustered index on a joining column(s) is often not useful for SQL, unless that index fully covers the query.

I have had...

I prefer to use CROSS APPLY to assign an alias name, like below.  Just to show the capability, I also used a CROSS APPLY to get the CONCAT'd column value:...

If the app is going to be reading the entire table anyway, if possible, use a clustered columnstore index.

Interesting article which brought up a great point about things to consider when writing code.

But you need to be fair to the "combined" code version.  So let me...

If you want blanks rather than NULL, then:

CASE WHEN ALTotalUnits / TotalProfiledHours >= 3 THEN CAST(ALTotalUnits AS varchar(30)) ELSE '' END AS [Higher than 3 Weeks],
CASE WHEN...

Any WHERE conditions in particular.  Typically StartTime is (almost) always compared against log-type tables.  And/or the data is summarized by StartTime periods (day, week, etc.).  If so, cluster the table...

CASE WHEN ALTotalUnits / TotalProfiledHours >= 3 THEN <higher than 3 weeks>
CASE WHEN ALTotalUnits / TotalProfiledHours < 3 THEN <lower than 3 weeks>

Alter the schema on those tables/views to 'dbo', then you should be able to view them just fine.

If it doesn't allow that, you'd have to script them out,...

Yeah, STUFF is good stuff!

Another thing to note is that you need to STUFF from right to left.  Otherwise the stuffed chars throw off the byte locations.

If you're literally using "*" in the view, refreshing the view is often needed too.  Not sure if that works for remote tables, but presumably it would (or SQL would...