Find Maximum(table.column1,table.column2,table.column3,...) as 'MaximumValue' from table SQL [duplicate]

In SQL Server 2008 and higher you could write a similar query to find greatest value among columns:

select col1
     , col2
     , col3
     , col4
     ,(select max(mx) 
         from (values(col1)
                     ,(col2)
                     ,(col3)
                     ,(col4)) t1(mx)
                ) as MaximumValue
from t1

SQL Fiddle Demo

Solution #1 (only SQL Server 2005+):

DECLARE @MyTable TABLE(Col1 INT,Col2 INT,Col3 INT,Col4 INT);
INSERT  @MyTable (Col1,Col2,Col3,Col4)
VALUES  (1,2,3,4);
INSERT  @MyTable (Col1,Col2,Col3,Col4)
VALUES  (100,20,300,40);
INSERT  @MyTable (Col1,Col2,Col3,Col4)
VALUES  (NULL,NULL,NULL,NULL);
INSERT  @MyTable (Col1,Col2,Col3,Col4)
VALUES  (NULL,1000,NULL,NULL);

SELECT  x.*,z.*
FROM    @MyTable x
CROSS APPLY(
    SELECT  MAX(y.Value) AS MaxOfValue,SUM(y.Value) AS SumOfValue
    FROM(
        SELECT x.Col1 AS Value
        UNION ALL 
        SELECT x.Col2
        UNION ALL 
        SELECT x.Col3
        UNION ALL 
        SELECT x.Col4       
    ) y
) z;

Results:

Col1 Col2 Col3 Col4 MaxOfValue SumOfValue
---- ---- ---- ---- ---------- ----------
1    2    3    4    4          10
100  20   300  40   300        460
NULL NULL NULL NULL NULL       NULL
NULL 1000 NULL NULL 1000       1000

Demo <- click

Solution #2 (SQL Server 2005+, Oracle) SQLFiddle demo(you can switch between SQL Server and Oracle)

SELECT a.*,b.MaxOfValue
FROM MyTable a
LEFT JOIN (
SELECT  unpvt.ID,
        MAX(unpvt.Value) AS MaxOfValue
FROM    MyTable x
UNPIVOT( Value FOR ColumnName IN (Col1,Col2,Col3,Col4) )unpvt
GROUP BY unpvt.ID
) b ON a.ID=b.ID;

You have labelled this with two entirely different databases. In Oracle, you do:

select greatest(col1, col2, col3, . . .)

SQL Server doesn't have a greatest() function, so you can use a giant case statement:

(case when col1 >= col2 and col1 >= col3 and col1 >= . . . then col1
      when col2 >= col3 and col2 >= . . .
      . . .
 end)

By the way, in both cases, you have to be careful if the column can contain NULL values.

This may not be the best solution but it works as expected (on SQL Server 2005+):

DECLARE @x xml= N''
SELECT t.col1, t.col2, t.col3, t.col4,
       @x.value('fn:max((sql:column("t.col1"), 
                         sql:column("t.col2"), 
                         sql:column("t.col3"), 
                         sql:column("t.col4")))', 'int')
FROM dbo.test140 t

See demo on SQLFiddle