Q: sql server find the difference between rows of same table I have a Table employees and for given empid, I would like to list the time, username and what has changed from previous time. Also for column 'req_type' I would like to write (normal) for 0 ,(important) for 1,(urgent) for value 2.
empid   Time                   req_type     token    requester   username    comment
------------------------------------------------------------------------------------------
10      05-01-2014 10:04:00     0             10      grusel       ann
10      05-01-2014 11:11:00     1              8                   ben       check site www:\\abc.com
10      05-01-2014 10:10:00     0              2                   ann 
12      06-01-2014 13:00:01     0              2       Jeena       bill      check www:\\someerror.com
12      07-02-2014 14:00:00     1              6        Mike       bill

Select statement should give me 3 rows for empid = 10 like shown below
Time                    Username          Changed
---------------------------------------------------------------------------
05-01-2014 11:11:00     ben          'req_type' updated from (normal) to (important), 
                                     'token' updated from 2 to 8,
                                     'requester' deleted,
                                     'comment' added check site www:\\abc.com
05-01-2014 10:10:00     ann          'token' updated from 10 to 2,
                                     'requester' added grusel
05-01-2014 10:04:00     ann           First entry

Similarly Select statement should give me 2 rows for empid = 12 like shown below
Time                    Username          Changed
----------------------------------------------------------------------------
7-02-2014 14:00:00     bill           'req_type' updated from (normal) to (important),
                                      'token' changed from 2 to 6,
                                      'requester' changed from Jeena to Mike,
                                      'comment' deleted
6-01-2014 13:00:01      bill           First entry


A: This is a partial answer we have to convert the final output to a space separated text by emp id 
CREATE TABLE emp (id int , [time] datetime, req_type int, token int )

INSERT INTO emp VALUES (1, GETDATE(), 0,1)
INSERT INTO emp VALUES (1, GETDATE()-1, 1,1)
INSERT INTO emp VALUES (1, GETDATE()-2, 1,3)

;WITH cte AS
(

SELECT *, ROW_NUMBER() OVER( PARTITION BY id ORDER BY time asc) rn  FROM emp 

),cte2 AS ( 
SELECT c2.*, CASE WHEN c.req_type != c2.req_type THEN 'Req changed from'+CONVERT(VARCHAR,c2.req_type)+' to '+CONVERT(VARCHAR,c.req_type)     ELSE '' END AS reqc, 
CASE WHEN c.token != c2.token THEN 'Token changed from' +CONVERT(VARCHAR,c2.token)+' to '+CONVERT(VARCHAR,c.token) ELSE '' END AS tokenc
 FROM cte c INNER join cte c2 ON c2.rn+1 = c.rn
 )

 SELECT id,time,req_type,reqc+tokenc AS changes FROM cte2 

