calculating maximum value and col name among rows

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  August 7, 2009 at 4:01 am

  #209063

  Hi,

  I have a table with some integer values.

  i want to calculate the maximum/minimum of each row and the column name which contributes it.

  exampl

  create table #tmp(v1 int,v2 int,v3 int,max1 int, colmax)

  i want to calculate the maximum among v1, v2 and v3 and whether v1 contributes or v2 contributes or v3 contributes the maximum value.

  if v1 -20

  v2 -30

  v3 -50

  then

  max1 will be 50

  colmax will be v3

  how can i achieve this in sql server 2005

  thanks,

  regards

  viji

• Paul White

  SSC Guru

  Points: 150467

  More actions

  August 7, 2009 at 8:05 am

  #1036012

  You could hack at it with CASE, but fundamentally the approach is flawed.

  If you store key-value pairs in rows instead of columns, you will find the task trivial.

  Paul

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi