asked on

Data to display differently-SQL Server

Hi All,

We are on SQL Server 2012 and we have a table that displays the data that's changed for a customer.
My task is to display in such a way that the left section has the initial data and the right section has the changed data.
i have embedded the grid that has this structure and how the desired structure is.
Please help.

WITH TBL_CUST(ID,NAME,DOB,UPDATED_BY,UPDATE_DT)
AS
(SELECT *
FROM (
VALUES
(1,'SAM','01/01/1981','MIKE','10/01/2006')
,(1,'SAMUEL','01/01/1982','JAMES','02/13/2007')
,(1,'SAMIEL','01/01/1982','JACOB','05/21/2010')
,(2,'DAVE','10/20/1978','SHARON','08/15/2015')
) AS A(ID,NAME,DOB,UPDATED_BY,UPDATE_DT)
)

Experts-Example.xlsx

Pawan Kumar

Try..

;WITH CTE
AS
(SELECT * , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID,DOB) rnk1
FROM (
VALUES 
   (1,'SAM','01/01/1981','MIKE','10/01/2006')
   ,(1,'SAMUEL','01/01/1982','JAMES','02/13/2007')
   ,(1,'SAMIEL','01/01/1982','JACOB','05/21/2010')
   ,(2,'DAVE','10/20/1978','SHARON','08/15/2015')
 ) AS A(ID,NAME,DOB,UPDATED_BY,UPDATE_DT)
)
,CTE2 AS
(	
	SELECT ID,NAME,DOB,UPDATE_DT,rnk FROM 
	(
		SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID,DOB) rnk FROM CTE
	)p 
)
SELECT a.ID, a.NAME , a.DOB , ISNULL(y.UPDATED_BY,'') UPDATED_BY , ISNULL(y.UPDATE_DT,'') UPDATE_DT , ISNULL(y.NAME,'') NAME, 
ISNULL(y.DOB,'') DOB FROM CTE2 a
OUTER APPLY
(
	SELECT TOP 1 * FROM CTE b
	WHERE a.Id = b.Id AND b.rnk1 > a.rnk
	ORDER BY b.rnk1
)y

Open in new window

Output

ID          NAME   DOB        UPDATED_BY UPDATE_DT  NAME   DOB
----------- ------ ---------- ---------- ---------- ------ ----------
1           SAM    01/01/1981 JACOB      05/21/2010 SAMIEL 01/01/1982
1           SAMIEL 01/01/1982 JAMES      02/13/2007 SAMUEL 01/01/1982
1           SAMUEL 01/01/1982                              
2           DAVE   10/20/1978                              

(4 row(s) affected)

Open in new window

Hope it helps !!

pvsbandi

ASKER

Thanks Pawan! Almost there.
The only extra row is the third one, where there is no further change, but still shows up as a separate row.
Can we get rid of it?

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pvsbandi

ASKER

Thanks much!