SQL Puzzle | First and Second Record – Pivot Format

Tags

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

3 thoughts on “SQL Puzzle | First and Second Record – Pivot Format”

Pingback: SQL Puzzle | Dynamic Pivot Puzzle | Improving my SQL BI Skills
Gregor Kralj said:

May 24, 2019 at 1:46 PM

with cte as
(
select
*,
ROW_NUMBER() over (partition by PrimaryKey order by Score desc) as rnk
from aTest
),
cte_only_two_max as
(
select * from cte where rnk < 3
)
,cte_max_date as (
select PrimaryKey, MAX([1]) as DateField1, MAX([2]) as DateField2
from cte_only_two_max
pivot
(
MAX(DateField) for rnk in ([1], [2])
) as p
group by PrimaryKey
),
cte_max_score as
(
select PrimaryKey, MAX([1]) as Score1, MAX([2]) as Score2
from cte_only_two_max
pivot
(
MAX(Score) for rnk in ([1], [2])
) as p
group by PrimaryKey
)
select cte_max_date.PrimaryKey, Score1, Score2, DateField1, DateField2
from cte_max_score
inner join cte_max_date on cte_max_date.PrimaryKey = cte_max_score.PrimaryKey

LikeLike

revank219 said:

July 21, 2021 at 10:35 PM

with cte as (
select PrimaryKey,score,Datefield,
concat(‘score’,ROW_NUMBER()over(partition by PrimaryKey order by score desc))scorernk,
concat(‘Datefield’,ROW_NUMBER()over(partition by PrimaryKey order by score desc))Daternk
from aTest
),cte2 as (
select PrimaryKey,Score1,score2,DateField1,DateField2 from cte
pivot (
max(Score) for scorernk in (Score1,Score2)
)a
pivot(
max(Datefield)for Daternk in (DateField1,DateField2)
)b
)
select PrimaryKey,max(Score1)score1,max(score2)score2,max(DateField1)DateField1,max(DateField2)DateField2 from cte2 group by PrimaryKey

LikeLiked by 1 person

M	T	W	T	F	S	S
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30

Share this

Related

3 thoughts on “SQL Puzzle | First and Second Record – Pivot Format”

Leave a comment Cancel reply