1

I have a set of rows with values

enter image description here

I want the following OutPut with individual Max and min values

enter image description here

Sorry for the poor prntsceen. i dont know how to draw tables in stackoverflow.

Newton Sheikh
  • 1,376
  • 2
  • 19
  • 42
  • 1
    Look at this topic looks like your http://stackoverflow.com/questions/7097518/sql-selecting-min-value-from-row-data-not-column-data – Arbejdsglæde Apr 23 '13 at 09:54
  • that will only return me the max or min values. i want the whole table along with two new columns with max and min value. – Newton Sheikh Apr 23 '13 at 09:57
  • and ya if there is Null in the row the the min value should be NULL – Newton Sheikh Apr 23 '13 at 10:05
  • okay may be replace the NULL with a 0 but it should not be 1 for the first row – Newton Sheikh Apr 23 '13 at 10:06
  • Is there any ID in your table? Maybe you could use [PIVOT][1] to rotate the table and hence to obtain the min and the max values. [1]:http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx – Alberto De Caro Apr 23 '13 at 10:00
  • no thats the problem i dont have id. the table is actually getting created on the runtime. all i have is rowid via Row_Number() method – Newton Sheikh Apr 23 '13 at 10:01

3 Answers3

11
select value1, value2, value3, value4,
       [min]=(select min(value) from (
              select value1 union all
              select value2 union all
              select value3 union all
              select value4) X(value)),
       [max]=(select max(value) from (
              select value1 union all
              select value2 union all
              select value3 union all
              select value4) Y(value))
  from tbl;

To recognize NULLs as min values, use the below instead

select value1, value2, value3, value4,
       [min]=(select TOP(1) value from (
                 select value1 union all
                 select value2 union all
                 select value3 union all
                 select value4) X(value)
              ORDER BY value ASC),
       [max]=(select TOP(1) value from (
                 select value1 union all
                 select value2 union all
                 select value3 union all
                 select value4) X(value)
              ORDER BY value DESC)
  from tbl;
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
2

Try this one -

DECLARE @temp TABLE
(
      Value1 INT
    , Value2 INT
    , Value3 INT
    , Value4 INT
)

INSERT INTO @temp (Value1, Value2, Value3, Value4)
VALUES 
    (NULL, 1, 1, NULL),
    (NULL, 1, 2, NULL),
    (NULL, 2, 2, NULL),
    (NULL, 2, 2, NULL),
    (1, 1, 1, 1),
    (2, 2, 1, 2),
    (1, 1, 1, NULL),
    (2, 2, 3, 2),
    (2, 2, 2, 2),
    (1, 1, 1, 1)

SELECT
      Value1
    , Value2
    , Value3
    , Value4
    , MinValue = (
        SELECT TOP 1 value 
        FROM (
            SELECT value = value1
              UNION
            SELECT value2
              UNION
            SELECT value3
              UNION
            SELECT value4
        ) mn
        ORDER BY value
    )
    , MaxValue = (
        SELECT MAX(value) 
        FROM (
            SELECT value = value1
              UNION
            SELECT value2
              UNION
            SELECT value3
              UNION
            SELECT value4
        ) mx
    )
FROM @temp

Results window:

proff

Devart
  • 119,203
  • 23
  • 166
  • 186
1

Please try:

declare @T table
(
  id int, 
  num1 int, 
  num2 int, 
  num3 int,  
  num4 int,   
  num5 int
)

insert into @T values
(1,     6,      51,      NULL,      99,      34),
(2,     222,    251,     24,     299,     234),
(3,     NULL,   NULL,    NULL,   NULL,    NULL)

select * From @T

SELECT id,
(SELECT 
(CASE WHEN (num1+num2+num3+num4+num5) IS NULL THEN NULL 
    ELSE MIN(v) end) 
FROM (VALUES (num1), 
            (num2), 
            (num3), 
            (num4), 
            (num5)) AS value(v)) as [MinVal],
(SELECT MAX(v) 
FROM (VALUES (num1), 
            (num2), 
            (num3), 
            (num4), 
            (num5)) AS value(v)) as [MaxVal]
FROM @T
TechDo
  • 18,398
  • 3
  • 51
  • 64