Tags

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


SQL Puzzle | UnPivot Data using Cross Apply

In this puzzle you need to unpivot the data without using the UNPIVOT keyword, you have to use cross apply method.For more details please check the sample input and expected output.

Sample Input

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

Expected Output

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 5 NULL
LG AKB73896401 NEC 6 Play
LG AKB73896401 TC9012A 1 NULL
LG AKB73896401 TC9012A 2 NULL
LG AKB73896401 TC9012A 3 TV Volume Up
LG AKB73896401 TC9012A 4 TV Volume Down
LG AKB73896401 TC9012A 5 NULL
LG AKB73896401 TC9012A 6 NULL

Script

Use below script to create table and insert sample data into it.

--


--


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')
;


--

select * from remotedata

;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] INTO #pawan FROM CTE r
    PIVOT ( MIN(r.keyaction)
        FOR [Command] IN ([1],[2],[3],[4],[5],[6]) )t 

select * from #pawan

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--

SELECT remote
	   ,Protocol	   
	   ,command
	   ,keyaction
         FROM   #pawan
                CROSS apply 
(VALUES ([1],1),([2],2),([3],3),([4],4),([5],5),([6],6)) cs (keyaction,command)
--WHERE keyaction is not null 		

--

Output-1

--

/*------------------------
SELECT remote
	   ,Protocol	   
	   ,command
	   ,keyaction
         FROM   #pawan
                CROSS apply 
(VALUES ([1],1),([2],2),([3],3),([4],4),([5],5),([6],6)) cs (keyaction,command)
--WHERE keyaction is not null 
------------------------*/
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                  5           NULL
LG AKB73896401       NEC                  6           Play
LG AKB73896401       TC9012A              1           NULL
LG AKB73896401       TC9012A              2           NULL
LG AKB73896401       TC9012A              3           TV Volume Up
LG AKB73896401       TC9012A              4           TV Volume Down
LG AKB73896401       TC9012A              5           NULL
LG AKB73896401       TC9012A              6           NULL

(12 row(s) affected)

--

Solution – 2 | Remove Nulls also if required

--

SELECT remote
	   ,Protocol	   
	   ,command
	   ,keyaction
         FROM   #pawan
                CROSS apply 
(VALUES ([1],1),([2],2),([3],3),([4],4),([5],5),([6],6)) cs (keyaction,command)
WHERE keyaction is not null 		

--

Output-1

--

/*------------------------

SELECT remote
	   ,Protocol	   
	   ,command
	   ,keyaction
         FROM   #pawan
                CROSS apply 
(VALUES ([1],1),([2],2),([3],3),([4],4),([5],5),([6],6)) cs (keyaction,command)
WHERE keyaction is not null 
------------------------*/
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

(7 row(s) affected)



--

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