SQL Puzzle | First and Second Record – Pivot Format

Write a T-SQL to fetch first and Second Record in Pivot Format.
For more 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

--

CREATE TABLE aTest
(	
	  PrimaryKey INT
	, Score INT
	, DateField DATE
)
GO


INSERT INTO aTest VALUES
(1, 50, '1/1/2016'),
(2, 12, '1/1/2016'),
(1, 75, '1/12/2016'),
(1, 80, '1/15/2016'),
(2, 35, '1/16/2016')
GO

--

—

SOLUTION – 1

--

;WITH CTE AS
(
	SELECT * FROM 
	(
		SELECT DISTINCT PrimaryKey 
		FROM aTest
	)p
	CROSS APPLY
	(
		SELECT TOP 2 Score , DateField 
		FROM aTest b
		WHERE b.PrimaryKey = p.PrimaryKey
		ORDER BY b.DateField DESC
	)k
)
,CTE1 AS 
(
	SELECT *, CONCAT('Score',rnk) c1 , CONCAT('DateField',rnk) c2 FROM 
	(
		SELECT *, ROW_NUMBER() OVER (PARTITION BY PRIMARYKey Order BY DateField DESC) rnk
		FROM CTE 
	)k
)
SELECT PrimaryKey , MAX([Score1])[Score1],MAX([Score2])[Score2],MAX(DateField1)DateField1,MAX(DateField2)DateField2 FROM CTE1
PIVOT ( MAX(Score) FOR c1 IN ([Score1],[Score2]) )y1
PIVOT ( MAX(DateField) FOR c2 IN (DateField1,DateField2) )y1
GROUP BY PrimaryKey 

--

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