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

Name ClassNumber
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

Expected Output

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

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”.

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.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/