4

I want to get the minimum and maximum across multiple columns in a row. I found various working queries using CROSS APPLY, UPIVOT and VALUES, e.g. like this one:

select column1, column2, code1,
       (qty2), (qty3), (qty4), (qty5),
       MinVal, MaxVal 
from dummydata 
    cross apply (select MIN(NULLIF (d ,0)) MinVal, 
                        Max(d) MaxVal 
                 FROM (VALUES (qty2), 
                              (qty3), 
                              (qty4), 
                              (qty5)
                      ) AS a(d) 
                )  A

These all are working in SQL Server 2005 or above, but I need a solution for SQL Server 2000 (database compatibility level 80). How can I do that?

DDL:

CREATE TABLE [dbo].[dummydata](
[column1] [nvarchar](255) NULL,
[column2] [nvarchar](255) NULL,
[code1] [nvarchar](255) NULL,
[qty2] [float] NULL,
[qty3] [float] NULL,
.......
[qty20] [float] NULL)

Query output:

enter image description here

3
  • Simplest solution: upgrade. Nov 11, 2016 at 9:59
  • Are the columns 4 or more? Nov 11, 2016 at 10:05
  • dear , 20 columns
    – speedyraz
    Nov 11, 2016 at 10:09

1 Answer 1

5

It's not going to be pretty (and I have no 2000 installation to test) but this should work.

The pk is the PRIMARY KEY of the table (or a column with UNIQUE NOT NULL constraints):

SELECT d.column1, d.column2, d.code1,
       d.qty2, d.qty3, d.qty4, d.qty5, ..., d.qty20,
       m.MinVal, m.MaxVal 
FROM 
    dummydata AS d 
  JOIN
    ( SELECT q.pk,
             MIN(NULLIF(q.qty, 0)) AS MinVal, 
             MAX(q.qty) AS MaxVal 
      FROM 
        ( SELECT pk, qty2 AS qty FROM dummydata UNION ALL
          SELECT pk, qty3 FROM dummydata UNION ALL
          SELECT pk, qty4 FROM dummydata UNION ALL
          SELECT pk, qty5 FROM dummydata UNION ALL
          ...
          SELECT pk, qty20
        ) AS q
      GROUP BY pk
    ) AS m
    ON m.pk = d.pk ; 

Alternative is to use correlated subqueries in the SELECT list:

SELECT d.column1, d.column2, d.code1,
       d.qty2, d.qty3, d.qty4, d.qty5, ..., d.qty20,
       ( SELECT MIN(NULLIF(q.qty, 0)) 
         FROM 
           ( SELECT d.qty2 AS qty UNION ALL
             SELECT d.qty3 UNION ALL
             ...
             SELECT d.qty20
           ) AS q
       ) AS MinVal, 
       ( SELECT MAX(q.qty) 
         FROM 
           ( SELECT d.qty2 AS qty UNION ALL
             SELECT d.qty3 UNION ALL
             ...
             SELECT d.qty20
           ) AS q
       ) AS MaxVal 
FROM 
    dummydata AS d ;

Tested in rextester.com

10
  • i Have edited my question with create table for more clarification . as you their is no PK in my table and your second query it gives min,max values which are not in rows ..
    – speedyraz
    Nov 12, 2016 at 4:38
  • No PK and no UNIQUE constraint? Nov 12, 2016 at 9:16
  • Can you share us with 2-3 sample rows and the wrong output? This seems weird. Nov 12, 2016 at 9:17
  • hii .. I have edited my question image that shows output of your query .
    – speedyraz
    Nov 12, 2016 at 9:49
  • and last two are MinValue and MaxValue
    – speedyraz
    Nov 12, 2016 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.