1

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 Step B Step Y Last Step --------------------------------------------------------------------- 1/21/2013 1/24/2013 1/3/2013 1/5/2013 1/7/2013 Step W

This is a step in the right direction, but it appears to only work in SSRS 2008: http://www.bigator.com/2012/04/26/spothighlight-minimum-and-maximum-values-in-each-row-in-matrix-report-in-ssrs/

Improve this question

1 Answer 1

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Wow, that was fast. Looks like I'll be using unpivot after all. Thanks!
@Kon_ UNPIVOT is great when you have data that is not normalized.
This seems to have issues when different records have a date in common: SQL Fiddle with example. Any way around that?
@Kon_ you should post a new question with the new details.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.