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

PrimaryKey Score DateField
1 50 2016-01-01
2 12 2016-01-01
1 75 2016-01-12
1 80 2016-01-15
2 35 2016-01-16

Expected Output

PrimaryKey Score1 Score2 DateField1 DateField2
1 80 75 2016-01-15 2016-01-12
2 35 12 2016-01-16 2016-01-01

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