SQL Puzzle | The Updated By Puzzle – I

This puzzle is a variation in puzzle – https://msbiskills.com/2016/11/24/sql-puzzle-the-updated-by-puzzle/

Write a T-SQL query that produces the expected output. We have to show who has updated the name and when it was updated
Please check out the sample input and the expected output.

Sample Inputs

Expected Output

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data

--


;WITH CTE
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)
)
SELECT * FROM CTE

--

—

SOLUTION – 1

--

;WITH CTE
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)
)
,CTE2 AS
(	
	SELECT * , CONCAT('By',rnk) c1 , CONCAT('Dt',rnk) c2 FROM 
	(
		SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID,DOB) rnk FROM CTE
	)p 
)
SELECT Id, MIN(NAME) NAME, MIN(DOB) DOB , ISNULL(MAX([By1]),'') UpdatedBy 
,ISNULL(MAX([Dt1]),'') UpdatedDt,ISNULL(MAX([By2]),'') UpdatedBy ,ISNULL(MAX([Dt2]),'')UpdatedDt,ISNULL(MAX([By3]),'') UpdatedBy
,ISNULL(MAX([Dt3]),'') UpdatedDt FROM CTE2
PIVOT ( MAX(UPDATED_BY) FOR c1 IN ([By1],[By2],[By3]) )y1
PIVOT ( MAX(UPDATE_DT) FOR c2 IN ([Dt1],[Dt2],[Dt3]) )y2
GROUP BY Id

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com