To find the maximum value across multiple columns in SQL Server 2022, you can use several approaches depending on your requirements and the structure of your data. Here are a few methods to consider:

1. Using CASE Statement or IIF

You can use a CASE statement or IIF function to compare columns within a row and return the highest value. This method is straightforward but can get cumbersome with many columns.

SELECT
    id,
    CASE
        WHEN col1 >= col2 AND col1 >= col3 THEN col1
        WHEN col2 >= col1 AND col2 >= col3 THEN col2
        ELSE col3
    END AS MaxValue
FROM
    YourTable;

Or using IIF which is more concise but specific to SQL Server:

SELECT
    id,
    IIF(col1 >= col2 AND col1 >= col3, col1, IIF(col2 >= col3, col2, col3)) AS MaxValue
FROM
    YourTable;

2. Using CROSS APPLY with VALUES

The CROSS APPLY with VALUES method is more elegant and scalable for multiple columns. It’s especially useful if you have many columns to compare.

SELECT
    t.id,
    MAX(v.MaxValue) AS MaxValue
FROM
    YourTable t
CROSS APPLY
    (VALUES
        (col1),
        (col2),
        (col3)
    ) AS v(MaxValue)
GROUP BY
    t.id;

3. Using UNPIVOT

UNPIVOT is useful for transforming columns into rows. After unpivoting the columns, you can then easily identify the maximum value.

SELECT
    upvt.id,
    MAX(upvt.value) AS MaxValue
FROM
    (SELECT id, col1, col2, col3
     FROM YourTable) p
UNPIVOT
    (value FOR col IN (col1, col2, col3)) AS upvt
GROUP BY
    upvt.id;

4. Using a Custom Table-Valued Function

For complex scenarios or if you’re working with this operation frequently across different queries, you might consider encapsulating the logic into a table-valued function. This function can take the row’s values as input and return the maximum value, which you then can apply in your select statements.

Each of these methods has its own advantages depending on the specific needs of your query, such as the number of columns you’re comparing and the overall structure of your database. The CROSS APPLY with VALUES approach is generally the most flexible and concise for handling multiple columns.