Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Pivot Puzzle [Multiple Solutions – Best One]

This puzzle is really simple, you have to pivot the data. For each unique remote/protocol pairing, I want to see which of the bit codes are used and also count how many buttons on that remote use that protocol. Please check out the sample input and the expected output below-

Sample Input

Remote Protocol Command KeyAction
LG AKB73896401 NEC 1 Power
LG AKB73896401 NEC 2 Volume Up
LG AKB73896401 NEC 3 Volume Down
LG AKB73896401 NEC 4 Pause
LG AKB73896401 NEC 6 Play
LG AKB73896401 TC9012A 3 TV Volume Up
LG AKB73896401 TC9012A 4 TV Volume Down

Expected Output

Remote Protocol ButtonCount 1 2 3 4 5 6
LG AKB73896401 NEC 5 Power Volume Up Volume Down Pause NULL Play
LG AKB73896401 TC9012A 2 NULL NULL TV Volume Up TV Volume Down NULL NULL

Rules/Restrictions

  • The solution should be should use “SELECT” statement or a CTE.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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


--


create table remoteData(
	Remote varchar( 20 )
	, Protocol varchar( 20 )
	, Command smallint
	, KeyAction varchar( 20 )
	)
	
insert remoteData(
	Remote 
	, Protocol 
	, Command 
	, KeyAction 
	)
	values
		('LG AKB73896401', 'NEC', 1, 'Power')
		, ('LG AKB73896401', 'NEC', 2, 'Volume Up')
		, ('LG AKB73896401', 'NEC', 3, 'Volume Down')
		, ('LG AKB73896401', 'NEC', 4, 'Pause')
		, ('LG AKB73896401', 'NEC', 6, 'Play')
		, ('LG AKB73896401', 'TC9012A', 3, 'TV Volume Up')
		, ('LG AKB73896401', 'TC9012A', 4, 'TV Volume Down')
;


--

Solution 1


--

;WITH CTE AS
(
	SELECT *,COUNT(*) OVER (PARTITION BY Remote,Protocol) cnt
	FROM remoteData
)
SELECT t.Remote,t.Protocol,cnt ButtonCount,[1],[2],[3],[4],[5],[6] FROM CTE r
	PIVOT ( MIN(r.keyaction)
		FOR [Command] IN ([1],[2],[3],[4],[5],[6]) )t 


--

Solution 2


--

;WITH CTE AS
(
	SELECT *
	FROM remoteData
)
SELECT p.Remote,p.Protocol,cnt ButtonCount,[1],[2],[3],[4],[5],[6] 
FROM 
(
	SELECT t.Remote,t.Protocol,[1],[2],[3],[4],[5],[6] FROM CTE r
	PIVOT 
	(
		MIN(r.keyaction)
		FOR [Command] IN ([1],[2],[3],[4],[5],[6]) 
	)t 
) p
LEFT OUTER JOIN
(
	SELECT Remote,Protocol,COUNT(*) cnt FROM remoteData GROUP BY Remote,Protocol
)u
ON u.Protocol = p.Protocol AND u.Remote = p.Remote

--

Solution 3


--

SELECT TOP(10000)
	pvt.Remote
	, pvt.Protocol
	, rdj.ButtonCount
	, pvt.[1], pvt.[2], pvt.[3], pvt.[4], pvt.[5], pvt.[6]
FROM (
	SELECT     
		rd.Remote
		, rd.Protocol
		, rd.KeyAction
		, rd.Command
	FROM remoteData rd
	) As s
	PIVOT
	(
	MIN(s.keyaction)
	FOR [Command] IN (
		[1],[2],[3],[4],[5],[6]
		)
	) As PVT
	left outer join (
		select
			rd.Remote
			, rd.Protocol
			, count( 1 ) as ButtonCount
		from remoteData rd
		group by
			rd.Remote
			, rd.Protocol
		) rdj
		on rdj.Remote = pvt.Remote
		and rdj.Protocol = pvt.Protocol
;

--

Performance Analysis – Execution Plans Comparison

The Pivot Puzzle [Multiple Methods - Best One]

The Pivot Puzzle [Multiple Methods – Best One]

Clearly second and the third method are the best one. !!

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com