SQL Puzzle | PIVOT Data via ClassNumber + Vertical Sorting of Data in each column

In this puzzle you have to pivot the data according to ClassNumber. We also have to sort the each column alphabetically. Please check the sample input and the expected output.

Sample Input

Expected Output

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

--

CREATE TABLE Class
(
	 [Name] VARCHAR(50)
	,ClassNumber VARCHAR(25)
)
GO

INSERT INTO Class VALUES
('Samira','Class X'),
('Julan','Class IX'),
('Marru','Class IX'),
('Meira','Class XII'),
('Ashwin','Class VIII'),
('Ketty','Class VIII'),
('Christina','Class VIII'),
('Janet','Class IX'),
('Jen','Class X'),
('Priyanshu','Class XII')
GO

SELECT * FROM Class
GO

--

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

--

;WITH CTE AS
(
	SELECT * 	
	, ROW_NUMBER() OVER (PARTITION BY ClassNumber ORDER BY Name) rnk1
	, DENSE_RANK() OVER (ORDER BY ClassNumber) rnk
	FROM Class
)
SELECT MAX([1]) [Class VIII], MAX([2]) [Class IX],MAX([3]) [Class X], MAX([4]) [Class XI]
FROM CTE 
PIVOT 
(
	MIN([Name]) FOR rnk IN ( [1],[2],[3],[4] )
)y
GROUP BY rnk1

--

Output-1

--                            


Class VIII      Class IX      Class X      Class XI
--------------- ------------- ------------ ----------------
Janet           Ashwin        Jen          Meira
Julan           Christina     Samira       Priyanshu
Marru           Ketty         NULL         NULL


(3 rows affected)

--

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

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com