Find Maximum Value in each Row – SQL Server

Here’s a simple and efficient way to find the maximum value in each row using SQL Server UNPIVOT

DECLARE @t table (id int PRIMARY KEY, col1 int, col2 int, col3 int)

-- SAMPLE DATA
INSERT INTO @t SELECT 1, 45, 2, 14
INSERT INTO @t SELECT 2, 8, 1, 12
INSERT INTO @t SELECT 3, 21, 20, 8
INSERT INTO @t SELECT 4, 8, 5, 2
INSERT INTO @t SELECT 5, 23, 49, 7
INSERT INTO @t SELECT 6, 19, 7, 5
INSERT INTO @t SELECT 7, 7, 7, 2
INSERT INTO @t SELECT 8, 14, 17, 2

-- QUERY
SELECT id, MAX(col) AS maxValue
FROM
(
SELECT id, col FROM @t
UNPIVOT
(col FOR ListofColumns IN (col1,col2,col3))
AS unpivott) AS p
GROUP BY id