Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ Group By XML Path Puzzle ] – In this puzzle we have to show distinct students and the courses & instructors comma separated. Please note that you have to use XML Path to solve this puzzle. Please check out the sample input and expected output for details.
Sample Input
StudentName | Course | Instructor | RoomNo |
Mark | Algebra | Dr. James | 101 |
Mark | Maths | Dr. Jones | 201 |
Joe | Algebra | Dr. James | 101 |
Joe | Science | Dr. Ross | 301 |
Joe | Geography | Dr. Lisa | 401 |
Jenny | Algebra | Dr. James | 101 |
Expected Output
StudentName | Taught by |
Jenny | Algebra by Dr. James in Room No 101 |
Joe | Algebra by Dr. James in Room No 101, Science by Dr. Ross in Room No 301, Geography by Dr. Lisa in Room No 401 |
Mark | Algebra by Dr. James in Room No 101, Maths by Dr. Jones in Room No 201 |
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
Script
Use the below script to generate the source table and fill them up with the sample data.
--CREATE TABLE CREATE TABLE TestTable ( StudentName VARCHAR(100) , Course VARCHAR(100) , Instructor VARCHAR(100) , RoomNo VARCHAR(100) ) GO -- Populate table INSERT INTO TestTable (StudentName, Course, Instructor, RoomNo) SELECT 'Mark', 'Algebra', 'Dr. James', '101' UNION ALL SELECT 'Mark', 'Maths', 'Dr. Jones', '201' UNION ALL SELECT 'Joe', 'Algebra', 'Dr. James', '101' UNION ALL SELECT 'Joe', 'Science', 'Dr. Ross', '301' UNION ALL SELECT 'Joe', 'Geography', 'Dr. Lisa', '401' UNION ALL SELECT 'Jenny', 'Algebra', 'Dr. James', '101' GO -- Check orginal data SELECT StudentName, Course, Instructor, RoomNo FROM TestTable GO |
Update May 14 | Solution
-- /************ SOLUTION 1 | Pawan Kumar Khowal ****************/ SELECT b.StudentName , STUFF (( SELECT ', ' + Course + ' by ' + CAST(Instructor AS VARCHAR(MAX)) + ' in Room No ' + CAST(RoomNo AS VARCHAR(MAX)) FROM TestTable a WHERE ( a.StudentName = b.StudentName ) FOR XML PATH('') ) ,1,2,'') AS cusr FROM TestTable b GROUP BY b.StudentName -- |
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning