How can I create a column containing the maximum across columns in SQL Server-CodePudding

I have a table CHA with many columns. There are two sets of 5 columns containing numeric data. I am looking to aggregate this data.

Select for the table below:

SELECT        Project_Num, Project_Title_Full, PTL, LOB, PL, JobTitle, [Function], MGR, MonthNum, 
              DMD1, DMD0, DMD2, DMD3, DMD4, DRC1, DRC0, DRC2, DRC3, DRC4, CHG0
FROM            Reporter.CHA

I am looking to add some aggregate functions DMD_Min, DMD_Max, etc.

I have the following syntax for the new value, but I keep getting an error.

(SELECT MIN(MIN_DMD) FROM (VALUES (DMD0), (DMD1), (DMD2), (DMD3), (DMD4)) AS value(MIN_DMD)) AS MIN_DMD

Can anyone proved a better method for finding the row by row aggregate across columns?

Edit:

The error I am seeing in SQL server is:

Error in FROM clause: near 'Values'

Error in FROM clause: near ')'

Unable to parse query text.

This happens when I try to update the query from the select statement above to add in the DMD_MIN column as below:

SELECT        Project_Num, Project_Title_Full, PTL, LOB, PL, 
JobTitle, [Function], MGR, MonthNum, DMD1, DMD0, DMD2, DMD3, DMD4, 
DRC1, DRC0, DRC2, DRC3, DRC4, CHG0, 
(SELECT MIN(MIN_DMD) FROM (VALUES 
(DMD0), (DMD1), (DMD2), (DMD3), (DMD4)) AS value(MIN_DMD)) AS MIN_DMD
FROM            Reporter.Compare_HA_Unpivot

CodePudding user response：

To Larnu's point, your table design is bad and it's leading you to write painful tedious sql statements. Instead consider as a half-way between complete-redesign and tedious-sql something like:

SELECT Project_Num, JobTitle, [Function], DMDtype, Min(DMD), Max(DMD)
FROM
    (
        SELECT Project_Num, JobTitle, [Function], 'DMD0' as DMDType, DMD0 FROM Reporter.Cha
        UNION ALL
        SELECT Project_Num, JobTitle, [Function], 'DMD1', DMD1 FROM Reporter.Cha
        UNION ALL
        SELECT Project_Num, JobTitle, [Function], 'DMD2', DMD2 FROM Reporter.Cha
        UNION ALL
        SELECT Project_Num, JobTitle, [Function], 'DMD3', DMD3 FROM Reporter.Cha
        UNION ALL
        SELECT Project_Num, JobTitle, [Function], 'DMD4', DMD5 FROM Reporter.Cha
) sub
GROUP BY Project_Num, JobTitle, [Function], DMDtype;

If you want the min and max across all DMD columns, then just remove dmdtype from your GROUP BY and SELECT. Ultimately, consider changing your table to look the resultset from that UNION subquery.

CodePudding user response：

I hesitate to offer a solution that masks the bad data design that @Larnu points out, but you could also create a user-defined function that would simplify your query.

create function udfBiggestOfFive 
(
     @arg1 as float = null
    ,@arg2 as float = null
    ,@arg3 as float = null
    ,@arg4 as float = null
    ,@arg5 as float = null
)
returns float
as
begin
    
    declare @result as float
    
    set @result = @arg1

    if @result < @arg2 set @result = @arg2
    if @result < @arg3 set @result = @arg3
    if @result < @arg4 set @result = @arg4
    if @result < @arg5 set @result = @arg5

    return @result
end

Then your query could be as simple as:

select        
    Project_Num, Project_Title_Full, PTL, LOB, PL, JobTitle, [Function], MGR, MonthNum, 
    dbo.BiggestOfFive(DMD1, DMD0, DMD2, DMD3, DMD4) as LargestDmd,
    dbo.BiggestOfFive(DRC1, DRC0, DRC2, DRC3, DRC4) as LargestDrc,
    CHG0
from Reporter.CHA

You would create another function for smallest-of-five, if that's what you're interested in (your question kinda jumps around on that) and you'd also want to use Coalesce judiciously if you're not sure there will always be values instead of nulls. But hopefully you get the point.

But... rethink your data design - normalize it. You'll be glad you did. Even this function is a bit silly in its rigid design.