SQL Server - aggregate if only one distinct value + nulls without ansi warnings

Question

Suppose I have a data like this

first_name    last_name     city
John          Bon Jovi      null
John          Lennon        null
John          Deer          null

And I want to create aggregating query which will return json which looks like this

{ "first_name": "John", "city": null }

Essentially, the query should check if there's only one distinct value within each column and if it is, put this value to json. All non-null columns are relatively easy to get with a query like this:

select
    case when count(distinct first_name) = 1 then max(first_name) end as first_name,
    case when count(distinct last_name) = 1 then max(last_name) end as last_name,
    case when count(distinct city) = 1 then max(city) end as city
from ...
for json path, without_array_wrapper

or

select
    case when max(first_name) = min(first_name) then max(first_name) end as first_name,
    case when max(last_name) = min(last_name) then max(last_name) end as last_name,
    case when max(city) = min(city) then max(city) end as city
from ...
for json path, without_array_wrapper

The result of the queries above is json like this {"first_name":"John"}. But then there are problems with nulls. Problem (1) - queries above do not take nulls into account, so if I have data like this

first_name    last_name     city
----------------------------------
John          Lennon        null
John          Lennon        null
John          null          null

Then last name is also included in the resulting json

{ "first_name": "John", "last_name": "Lennon" }

Ok, that's understandable (cause ...Null value is eliminated by an aggregate...) and I can solve it with a query like this:

select
    case when count(distinct first_name) = 1 and count(first_name) = count(*) then max(first_name) end as first_name,
    case when count(distinct last_name) = 1 and count(last_name) = count(*) then max(last_name) end as last_name,
    case when count(distinct city) = 1 and count(city) = count(*) then max(city) end as city
from ...
for json path, without_array_wrapper

But there are other problems with nulls I can't really solve neatly for now. Problem (2) - I want to have also "city":null in my json. Of course I can do something like this

...
case when count(city) = 0 then 'null' end as city
...

and then replace string null with real nulls, but it's not very neat. Another annoying thing is (3) - I'd really like to get rid of warnings

Warning: Null value is eliminated by an aggregate or other SET operation.

without turning ANSI_WARNINGS off. For now I can only think about using some placeholders with isnull which doesn't look like a clean solution

...
case when count(distinct isnull(city, 'null')) = 1 then max(city) end as city
...

So, any ideas on how to elegantly solve problems (2) and (3)? see examples in db<>fiddle.

Answer 1

Ok, so nobody posted any answers so far, I have thought of one way doing it. It's not perfect, but it seems to work. So the idea is to use @var = @var + 1 trick inside of select. But it should be a bit more complicated:

declare
    @first_name varchar(4), @first_name_state tinyint = 0,
    @last_name varchar(4), @last_name_state tinyint = 0,
    @city varchar(4), @city_state tinyint = 0,
    @country varchar(10), @country_state tinyint = 0,
    @result nvarchar(max) = '{}';

select
    @first_name_state =
        case
            when @first_name_state = 0 then 1
            when @first_name_state = 1 and @first_name = t.first_name then 1
            when @first_name_state = 1 and @first_name is null and t.first_name is null then 1
            else 2
        end,
    @first_name = t.first_name,
    @last_name_state =
        case
            when @last_name_state = 0 then 1
            when @last_name_state = 1 and @last_name = t.last_name then 1
            when @last_name_state = 1 and @last_name is null and t.last_name is null then 1
            else 2
        end,
    @last_name = t.last_name,
    @city_state =
        case
            when @city_state = 0 then 1
            when @city_state = 1 and @city = t.city then 1
            when @city_state = 1 and @city is null and t.city is null then 1
            else 2
        end,
    @city = t.city,
    @country_state =
        case
            when @country_state = 0 then 1
            when @country_state = 1 and @country = t.country then 1
            when @country_state = 1 and @country is null and t.country is null then 1
            else 2
        end,
    @country = t.country
from Table1 as t;

if @first_name_state = 1
    set @result = json_modify(json_modify(@result,'$.first_name','null'),'strict $.first_name',@first_name);

if @last_name_state = 1
    set @result = json_modify(json_modify(@result,'$.last_name','null'),'strict $.last_name',@last_name);

if @city_state = 1
    set @result = json_modify(json_modify(@result,'$.city','null'),'strict $.city',@city);    

if @country_state = 1
    set @result = json_modify(json_modify(@result,'$.country','null'),'strict $.country',@country);    

select @result;
----------------------------------
{"first_name":"John","city":null}

see db<>fiddle with examples.

Please note that, according to Microsoft docs you shouldn't use this variable aggregation assignment trick cause some of the statements can be called more than once.

Don't use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur. Because, all expressions in the SELECT list (including assignments) aren't necessarily run exactly once for each output row.

I hope in this case it should work fine cause it's not exactly an aggregation, and it's ok if these statements will be called more than once per row. Still, you can find some useful links in this answer.