Ssrs 2005 Find Name Of Column With Max Value

The column on the far right is what I'm trying to add to my report. Is this possible to do without modifying the query to use something like Unpivot? Step X Step W Step A

Solution 1:

You can use the UNPIVOT function and a CROSS APPLY to get this:

;with cte as
(
  select col, value
  from yourtable
  unpivot
  (
    value
    for col in ([Step X], [Step W], [Step A], [Step B], [Step Y])
  ) unpiv
) 
select [Step X], 
  [Step W], 
  [Step A], 
  [Step B], 
  [Step Y],
  d.col LastStep
from yourtable
cross apply
(
  select c1.col
  from cte c1
  inner join
  (
    select max(value) MaxDate
    from cte
  ) c2
    on c1.value = c2.maxdate
) d

See SQL Fiddle with Demo