How to find Minimum and Maximum value across multiple Columns in SQL Server

How to return Minimum and Maximum value across multiple Columns in SQL Server

Scenario:
Suppose we have a sample Products table with Price values across multiple Years as shown below.

/* Create a sample table */

CREATE TABLE [dbo].[tbl_Products]

  (

    [ProdID] [int] NOT NULL,

    [ProdName] [varchar](255) NOT NULL,

    [YR2010] [int] NULL,

    [2011] [int] NULL,

    [YR2012] [int] NULL,

    [2013] [int] NULL,

    [YR2014] [int] NULL,

    [2015] [int] NULL,

  ) ON [PRIMARY]

GO

/* Insert sample records */

INSERT INTO [dbo].[tbl_Products]

  ([ProdID], [ProdName], [YR2010], [2011], [YR2012], [2013], [YR2014], [2015])

  VALUES (121, 'Apple',25, NULL,35,15,20,10), (122,'Mango',35,15, NULL,45,15,25)

SELECT * FROM [dbo].[tbl_Products];

Now we can find the Minimum and Maximum value for each Product across the multiple Year columns using the below Query:

/* Returning Minimum and Maximum values across Multiple Columns */

SELECT [ProdID],[ProdName],

    (

      SELECT MIN(Val)

      FROM ( VALUES ([YR2010]),([2011]),([YR2012]),([2013]),([YR2014]),([2015]))

      AS tbl_Prod(Val)

    ) MinVal,

    (

    SELECT MAX(Val)

      FROM ( VALUES ([YR2010]),([2011]),([YR2012]),([2013]),([YR2014]),([2015]))

      AS tbl_Prod(Val)

    ) MaxVal

FROM [dbo].[tbl_Products]

Result:

We can understand the above query with below simple example:

SELECT *

  FROM ( VALUES (1),(NULL),(3),(17),(5),(13)) AS tbl(Col1)

Result:

SELECT MAX(Val) MaxVal, MIN(Val) MinVal

  FROM ( VALUES (7),(NULL),(3),(17),(5),(13)) AS tbl(Val)

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------