Select distinct current and previous columns from

Question

I have a table like so

   Id  |Name    |Status |   Rate |  Method  |ModifiedTime             |ModifiedBy
-----------------------------------------------------------------------------
    1  |Recipe1 |  0    |    30  |   xyz    | 2016-07-26 14:55:57.977 |     A
-------------------------------------------------------------------------------
    2  |Recipe1 |  0    |    30  |   abc    | 2016-07-26 14:56:18.123 |     A
--------------------------------------------------------------------------------
    3  |Recipe1 |  1    |    30  |   xyz    | 2016-07-26 14:57:50.180 |     b

I would like to select only the changes and wanted to show what the value was previously and what it is currently accompanied by who changed it. The final outcome will be as follows. I am using SQL Server 2014.

Item    | Before |  After |ModifiedTime             | ModifiedBy
-----------------------------------------------------------------------------
Method  |  xyz   |  Abc   | 2016-07-26 14:56:18.123 |   A
-------------------------------------------------------------------------------
Status  |  0     |  1     | 2016-07-26 14:57:50.180 |   b
--------------------------------------------------------------------------------
Method  |  Abc   |  xyz   | 2016-07-26 14:57:50.180 |   b

Answer 1

without dynamic SQL this is the best I could do. I'm assuming that you are grouping changes by Name. If Name can change it should be removed of the partition by and added to the items subquery and to the case. You can try it here

select item, 
       case item
         when 'Status' then cast(prevStatus as varchar)
         when 'Rate' then cast(prevRate as varchar)
         when 'Method' then prevMethod
         end as Before, 
       case item
         when 'Status' then cast(Status as varchar)
         when 'Rate' then cast(Rate as varchar)
         when 'Method' then Method
         end as After,
         ModifiedTime,
         ModifiedBy
  from (
    select Status,
           lag(Status) over (partition by Name order by id) prevStatus,
           Rate,
           lag(Rate) over (partition by Name order by id) prevRate,
           Method,
           lag(Method) over (partition by Name order by id) prevMethod,
           ModifiedBy,
           ModifiedTime
      from t ) as t1 cross join  (select 'Status' as item union all 
                                  select 'Rate' as item union all 
                                  select 'Method' as item) items
where (item = 'Status' and Status <> prevStatus)
   or (item = 'Rate' and Rate <> prevRate)
   or (item = 'Method' and Method <> prevMethod)
order by ModifiedTime

OUTPUT

item   Before After ModifiedTime            ModifiedBy 
------ ------ ----- ----------------------- ---------- 
Method xyz    abc   2016-07-26 14:56:18.123 A          
Status 0      1     2016-07-26 14:57:50.180 b          
Method abc    xyz   2016-07-26 14:57:50.180 b   

On the second row you'll see that ModifiedBy is b while you have A. I think is a typo.