Search code examples
sqlsql-servermaxminunpivot

SQL Retrieving Min & Max Across Multiple Columns Using UNPIVOT

Can someone explain to me why this query is not working as expected please?

I want to get the minimum and maximum values for each order, where the values span across multiple columns and there can be multiple rows for orders. Also, not all 5 value columns will contain data.

The table is structured like so:

Order | Value_A | Value_B | Value_C | Value_D | Value_E
00001 | 10.20   | 15.47   |         |         |         
00002 | 40.20   | 15.47   |         |         |         
00003 | 01.80   |         |         |         |         
00004 | 18.99   | 18.99   | 18.99   | 18.99   | 18.99

And my query is this:

SELECT  Order, MIN(v) AS MinValue, MAX(v) AS MaxValue
FROM    Table_Test
UNPIVOT ( v FOR nValue IN ( Value_A, Value_B, Value_C, Value_D, Value_E ) ) as U
GROUP BY Order
ORDER BY Order

It works almost perfectly but for some reason MinValues returns as nothing for all orders.

Any help will be greatly appreciated.

Many thanks.

[Edit]

I've just realised the data type is nvarchar(50), not decimal :( (I inherited this table by the way). I guess I need to convert to decimal and all will be good.

Solution

  • The data type for the Value columns was set to nvarchar(50), where it should be decimal.