add a column and get other column names as its value

• sql84

  SSC Eights!

  Points: 830

  More actions

  February 4, 2014 at 8:11 pm

  #283133

  here is a sample table

  DECLARE @t TABLE(a INT,b INT,c INT);

  INSERT @t VALUES(1,2,3),(9,8,7),(4,6,5);

  SELECT *

  , ( SELECT MAX(val)

  FROM (VALUES (a)

  , (b)

  , (c)

  ) AS value(val)

  ) AS MaxVal

  FROM @t;

  result:

  A---B---C---MAX

  1---2---3---3

  9---8---7---9

  4---6---5---6

  i would like to add a column 'Max_cols' which will have other column names as its values. column names are respective to the values that column 'MAX' has in it ...(hope this makes sense).

  the table should look like this.

  A---B---C---MAX---Max_cols

  1---2---3---3-------C

  9---8---7---9-------A

  4---6---5---6-------B

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  February 4, 2014 at 9:11 pm

  #1686080

  For those that still work in 2005 that would like to play with such a thing, I've reworked the sample data to work in 2005 and up. This will do it although a fairly complex set of CASE statements would likely be faster. It also won't list more than 1 column in the case of ties although it will consistently show which column (by column name) was the first to have the max value.

  Unlike the faster but more complicated CASE method (which I've not done here), this method makes it easy to add other columns to the mix.

  Of course, if you still have problems with this after the following code example, then post some readily consumable data and an example of the output as previously requested using the link I provided to learn how to post readily consumable data correctly.

  DECLARE @t TABLE(a INT,b INT,c INT);

  INSERT @t

  (a,b,c)

  SELECT 1,2,3 UNION ALL

  SELECT 9,8,7 UNION ALL

  SELECT 4,6,5 UNION ALL

  SELECT 3,4,4 UNION ALL

  SELECT 4,4,3 UNION ALL

  SELECT 4,3,4

  ;

  SELECT A=t.a, B=t.b, C=t.c, [Max]=ca.ColValue, MaxCol=ca.ColName

  FROM @t t

  CROSS APPLY

  ( --=== Short Sort instead of using MAX

  SELECT TOP 1

  s.ColName,s.ColValue

  FROM ( --=== Unpivot and name the columns

  SELECT 'a',a UNION ALL

  SELECT 'b',b UNION ALL

  SELECT 'c',c

  ) s (ColName,ColValue)

  ORDER BY s.ColValue DESC, s.ColName

  ) ca (ColName,ColValue)

  ;

  Results...

  A B C Max MaxCol

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

  1 2 3 3 c

  9 8 7 9 a

  4 6 5 6 b

  3 4 4 4 b

  4 4 3 4 a

  4 3 4 4 a

  (6 row(s) affected)

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)