Ha I think's the other way around.  CASE WHEN is an abomination of over wordiness that never should've been created.  First, back in the day you used to have to...

Imo IIF gets hated on because it was available first in MS Access.  IIF gets interpreted back to "CASE WHEN" so it's really the same (which is a shame because...

It also works without the tally table and just using CROSS APPLY

select
  po.ProductCategory, po.ProductSubCategory, v.Region, v.OrdersPlaced,
  (v.OrdersPlaced*100.0/SUM(v.OrdersPlaced) over (partition by po.ProductCategory)) PercentPlaced
from #po po
 ...

Both work.  The tally based is probably more efficient depending on scale.  It didn't occur to use a tally table...

Ok ok I changed the decode to 'South Africa' and now it does work.  Misdiagnosed due to misleading code?  Yea, if it scans once and there's no dependency then it...

Wait a sec.  North Africa is in the code twice!

The bottom grouping is the code with tally ordered by 1,2,3.  The top is UNION ALL with same ordering

When CROSS JOIN with the tally table the resulting ordering may not align with Region.  It's not known which region will be 1 or 2 or ... unless joined back...

When the code posted above runs it produces the following output:

CountryStatenamesurnameagegender
USAlabamaJohnDon45NULL
CanadaTorontoMarkBagwell35Male

This is exactly what was requested no?  Is it a requirement the query be dynamic because the...

drop table if exists dbo.TestAZURE_JSON;
go
CREATE TABLE dbo.TestAZURE_JSON (
RowID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
Country varchar(10),
[State] varchar(10),
[JSON_Value] nvarchar(max));
go

INSERT dbo.TestAZURE_JSON(Country, [State], [JSON_Value]) VALUES
('US', 'Alabama', '{"name":"John","surname":"Don","age":45}'),
('Canada', 'Toronto', '{"name":"Mark","surname":"Bagwell","age":35,"gender":"Male"}');

select 
...

• This reply was modified 5 years, 7 months ago by Steve Collins. Reason: made JSON nvarchar(max)

This uses the daterange function described here:

https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

(the actual daterange code used is closest to this one)

drop table if exists dbo.TestUserAccount;
go
create table dbo.TestUserAccount(
  AccountName varchar(20) primary...

;with
details_summary_cte(Manage_GUID, details_row_count) as (
    select Manage_GUID,
           count(*) /* ... other aggregate functions */
 ...

You could successively cross apply

declare
  @price        int=100;

;with some_discounts_cte(first_pct, second_pct, third_pct) as (
    select 20.0, 20.0, 50.0)
select
...

drop table if exists #rev;
go
create table #rev(
  RevisionId int,
  AssemblySiteId int,
  Status nvarchar(200));
go

insert into #rev(RevisionId,AssemblySiteId)
values
(1900,200),
(2000,300),
(5000,800);

drop table if exists #location;
go
create table #location(
  locRevisionId int,
...