Tags

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


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

http://MSBISkills.com