sqlsql-servertable-functions

Row wise statistical mode in table function


I have a table in SQL Server called SurveyData found here

That looks like this:

-- Create the table
CREATE TABLE SurveyData 
(
    country VARCHAR(50),
    year INT,
    val1 INT,
    val2 INT,
    val3 INT
);

-- Insert 10 rows of data
INSERT INTO SurveyData (country, year, val1, val2, val3) 
VALUES ('USA', 2020, 4, 4, 5),
       ('Canada', 2021, 2, 4, 3),
       ('Germany', 2022, 5, 5, 4),
       ('France', 2020, 3, 4, 2),
       ('UK', 2021, 4, 2, 3),
       ('Australia', 2022, 3, 3, 4),
       ('Italy', 2020, 5, 5, 5),
       ('Spain', 2021, 1, 2, 3),
       ('Mexico', 2022, 4, 4, 4),
       ('Brazil', 2020, 2, 3, 1);

-- Add the mode column to the table
SELECT * FROM SurveyData

I want to create a table function that will take two parameters declared country =USA and year =2021 and the output will be the filtered table df by country USA and year 2021 and the third column will be the statistical mode (most frequent) of columns val1, val2 and val3 but row wise. How can I do it in SQL Server?


Solution

  • See example

    -- parameters
    declare @Country varchar(50)= 'USA', @Year int= 2020;
    
    select *
    from df
    cross apply( select top 1 val,count(*) cnt from ( values (val1),(val2),(val3))t(val)
                 group by val
                 order by count(*) desc
              )a
    where country=@country and year=@year
    
    country year val1 val2 val3 val cnt
    USA 2020 4 4 5 4 2

    fiddle